Data Validation error on Update - date field

[TinaCa]

I am trying to update the date field in a table (with the current date as the invoice date). When I run the VBA code I get a data validation error. I checked the talbe and the field is a date format. i even set it to short date and still get the error. Here's the update code



InvDt = Date

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

[June7]

BillDt and InvDt are fields or variables?

I presume CompanyBillingDate is a Date field, need the # delimiters.

This date is saved in two tables?

[TinaCa]

BillDt is an integer, InvDt is a date

I companybillingdate is integer, even if it was a date I have other update and insert queries containing date fields from one table to another and they are running without #'s.

[June7]

Are BillDt and InvDt declared variables?

[TinaCa]

Yes, of course.

[TinaCa]

Ok first mistake it was trying to update the wrong table I think. I need to update the tblCompanyExtras not CompanyInformation. Now I am getting a syntax error...errrr

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

[June7]

Maybe need the # delimiters for the date variable. Is InvDt explicitely declared as a Date type?