Syntax Error on Update Statment

[TinaCa]

The system is giving a syntax error message and I swear I can't find it.



The idea is to update a table where the billingdate (integer) = BillDt (variable) and the DateBilled (i.e. the invoice date = null). Is supposed to set the BillingDate to todays date (variable InvDt).

DoCmd.RunSQL "UPDATE tblCompanyExtras " _
& "JOIN tblCompanyExtras ON CompanyInformation.CompanyAccountNo = tblCompanyExtras.CompanyId " _
& "SET tblCompanyExtras.DateBilled = " & InvDt & " " _
& "WHERE (((tblCompanyExtras.DateBilled) Is Null) AND ((CompanyInformation.CompanyBillingDate)= " & BillDt & "));"

I originally create the query in the query grid and copied the sql code to my sub routine. The original code said update the CompanyInformation table and was giving me data validation errors. The tblCompanyExtras is the table where I need to set the invoice date to date().

[kennejd]

You might try changing "WHERE (((tblCompanyExtras.DateBilled) Is Null) AND ((CompanyInformation.CompanyBillingDate)= " & BillDt & "));"

to
"WHERE (((tblCompanyExtras.DateBilled) Is Null) AND ((CompanyInformation.CompanyBillingDate)= #" & BillDt & "#));"

Dates need the pound signs in the criteria....assuming CompanyBillingDate is a date field...

[pbaldy]

I believe you also need to specify the join, ie INNER, LEFT or RIGHT.