Stuck with syntax for Module!

[papic1972]

Hi,

I've written a short module to update a field in my table:

Function TestUpdateQuery()
Dim strSQL As String
strSQL = ""

DoCmd.SetWarnings False
strSQL = "UPDATE tblLocal set [LoadBoxNo] =2" & " WHERE [LoadBoxNo] =100 And [TruckNo] = " & Forms!frmDriverEdit2.Form!txtTruckNo & ";"
CurrentDb.Execute (strSQL), dbSeeChanges + dbFailOnError
End Function



I need to filter by delivery date also, therefore i've added Delivery Date to the SQL statement:

strSQL = "UPDATE tblLocal set [LoadBoxNo] =2" & " WHERE [DeliveryDate] = " & Format(Forms!frmLoadAllocation.Form!cboAllocationDate.Column(0), "mm/dd/yyyy") And [LoadBoxNo] =100 And [TruckNo] = " & Forms!frmDriverEdit2.Form!txtTruckNo & ";"


but i'm getting a compile error. I can't seem to see where i'm going wrong. Can anyone help!

Thank you.

 

[boblarson]

[COLOR=black][FONT=Verdana]strSQL = "UPDATE tblLocal set [LoadBoxNo] = 2 WHERE [/FONT][/COLOR][COLOR=red][FONT=Verdana][COLOR=black][DeliveryDate] =[/COLOR] #" [COLOR=black]& Format(Forms!frmLoadAllocation.Form!cboAllocationDate.Column(0), "mm/dd/yyyy")[/COLOR] & “# [COLOR=black]And[/COLOR] [/FONT][/COLOR][COLOR=black][FONT=Verdana][LoadBoxNo] =100 And [TruckNo] = " & Forms!frmDriverEdit2.Form!txtTruckNo & ";"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]

[/FONT][/COLOR]

You were missing octothorpes (#) and the ampersand. Also, no need to use concatenation between

set [LoadBoxNo] = 2 WHERE [DeliveryDate] =

Just do it like I showed.

 

[papic1972]

Excellent Bob! Thank you for getting me out of a pickle once again!!