Hi guys

I am getting syntex error when i try to update multiple columns using the following code.

s2 = "(update tblmain set (timeactioned = #" & Format(Me.Text16.Value, "hh:mm:ss") & "#) and (timetoscanning= # " & Format(Me.Text18.Value, "hh:mm:ss") & " #) and (timescanned= #" & Format(Me.Text20.Value, "hh:mm:ss") & "#) and (numberofpages= " & Format(Me.Text22.Value, "hh:mm:ss") & ") and (scannedby='" & Format(Me.Text22.Value, "hh:mm:ss") & "') where BatchNo= " & Combo4.Value & ")"
DoCmd.RunSQL (s2)


Can anyone help me out in this.

Regards
AMAN

It looks like you invented this syntax on the fly, try and bulid it in the querybuilder with "dummy" data and modify it afterwards in VBA.

Eksample on a multiple updates:

UPDATE inputtable SET inputtable.[date] = "#31.12.2009#", inputtable.location = "test", inputtable.department = "2"
WHERE (((inputtable.bleepedBarcode)="none of your business"));


JR;)

this could be a million different things. check that the syntax for all the quotes is correct first, then you can go from there.

also, use the "," character for a delimiter using an UPDATE statement in SQL rather than the "and" word. that might be the problem.

Hi ya

Thanks for your reply. Well as i am new to programming so getting a lot syntex error etc.. But when i wrote the following simple update statement then again its giving an error
" Microsoft field can't find the field '|' reffered in your expression"

s2 = "UPDATE tblmain SET tblmain.TimeActioned =#" & Format(Me.[Text16].[Value], "hh:mm:ss") & "# WHERE (([tblmain].[BatchNo]=" & [frmupdate].[Combo4].[Value] & "));"
DoCmd.RunSQL (s2)



Also, the control stores the time in hh:mm:ss so I have set the format long date using input mask of timeactioned control.
But in the table,I have used date datatype of time actioned field. Is it ok or do i need to make changes in that too.

Please guide me in this
Thanks
Aman