How to update a field with matching info in the same table 1

[MICKI0220]

I have a table that has invoices and payments. The invoice line items have the invoice date and invoice number in the record filled in, the payment record has the payment date and payment amount filled in. They have the same invoice number and customer id. How do I set the invoice date in the payment record (which is blank) set to the date of the invoice record. I am doing an eligible for commision based on the invoice date, but the payments have a blank invoice date field. This is how I code it when updating separate tables, but I am having a brain block when doing it in the same table.


strSQL = "UPDATE tbl_ats_CommissionWork " & _
"INNER JOIN tbl_ats_Comm_Draw_Hist ON tbl_ats_CommissionWork.InvcNum = tbl_ats_Comm_Draw_Hist.Inv_Num " & _
"AND tbl_ats_CommissionWork.CustId = tbl_ats_Comm_Draw_Hist.Cust_ID " & _
"SET tbl_ats_CommissionWork.InvcDate = tbl_ats_comm_draw_hist.inv_date " & _
"WHERE tbl_ats_CommissionWork.InvcDate Is Null "


Thanks for any help. I hope I explained it well.

My table that I want to update the invdate is "tbl_ats_comm_draw_hist

 

[PHV]

What about this ?
strSQL = "UPDATE tbl_ats_Comm_Draw_Hist P " & _
"INNER JOIN tbl_ats_Comm_Draw_Hist I ON P.Inv_Num=I.Inv_Num " & _
"AND P.Cust_Id=I.Cust_ID " & _
"SET P.Inv_Date=I.Inv_Date " & _
"WHERE P.Inv_Date Is Null"



Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MICKI0220]

Thanks that worked great