SQL Statement not working

[geoffcodd]

I have the following code which won't work

DoCmd.RunSQL "UPDATE Points INNER JOIN DataWater ON Points.Id = DataWater.Point_Id " & _
"SET DataWater.Locked = -1, DataWater.Passed_To_Accounts = -1, DataWater.Last_Update = Now() " & _
"WHERE (((DataWater.Date_TaxPoint)=[Forms]![frmInvoices_For_Payment]![Date_TaxPoint]) AND (([Points].[Number] & " - " & Right([Invoice_Number],3))=[Forms]![frmInvoices_For_Payment]![Invoice_Number]));"

I get data type mismatch error

Any ideas
Thanks
Geoff

 

[Mile-O]

Check your datatypes on the connected fields

 

[geoffcodd]

The data types are fine as when i run it as a query it works fine it's only when I try to run it as code

thanks
geoff

 

[dcx693]

In the WHERE part of the SQL statement:
you need to place the field references outside the quotation marks like this:

"WHERE (((DataWater. Date_TaxPoint)=" & [Forms]![frmInvoices_For_Payment]![
Date_TaxPoint] & ") AND (([Points].[Number] & ' - ' & Right([Invoice_Number],3))=" & [Forms]![frmInvoices_Fo
r_Payment]![Invoice_Number] & "));"


Also, if Date_TaxPoint or Invoice_Number are text or date fields, you need to delimit them properly.