SQL syntax error

[ray147]

Hi guys,

I'm getting a syntax error in UPDATE statement when running this query through VBA..

any ideas would be appreciated!

tnx

DoCmd.RunSQL "UPDATE Bookings SET " & _
" [DateLastUpdate] = #" & DateLastUpdate & "#" & _
" ,[PlanDespDate] = " & PlanDespDate & _
" ,[PlanDespTime] = #" & PlanDespTime & "#" & _
" ,[Qty] = " & Qty & _
" ,[Shift] = " & Shift & _
" ,[TransMode] = " & TransMode & _
" ,[Comments] = " & Comments & _
" ,[Status] = " & Status & _
" WHERE BookingRef = " & BookingRefx & ";"

 

[nateobot]

This line ,[PlanDespDate] = " & PlanDespDate & _ be formatted as a date like the other date lines.

Are all the columns below numbers? If not you will need to surround them with quotes or apostrophes.

" ,[Qty] = " & Qty & _
" ,[Shift] = " & Shift & _
" ,[TransMode] = " & TransMode & _
" ,[Comments] = " & Comments & _
" ,[Status] = " & Status & _
" WHERE BookingRef = " & BookingRefx & ";"

example:

" ,[Comments] = '" & Comments & "'" & _

If you still have errors, post what the actual SQL is when your program runs.

 

[reclusivemonkey]

" ,[PlanDespDate] = " & PlanDespDate & _
" ,[PlanDespTime] = #" & PlanDespTime & "#" & _

You've got your #'s wrapped round the time, not the date.

 

[ray147]

working!

ok guys, sorted! i sorted out the apostrophies for some variable numbers, but i had another problem..which was a text box 'PlanDespDate' which wasn't bound to a control source.

tnx again

 

[ray147]

again can u assist?!

hi again,
the update query is working,
however i have a little problem when the user does not enter the value for the date and time fields..i get this error since the '#' still come up ...

Run-time error 3075:

Syntax error in date in query expression '##'


can i put something in within the SQL code to avoid this problem? or the only option is to program some IF statement to check whether the fields are empty and vary the SQL code without the '#' accordingly?

any help appreciated, thanks

 

[nateobot]

hi again,
or the only option is to program some IF statement to check whether the fields are empty and vary the SQL code without the '#' accordingly?

any help appreciated, thanks

This is what I would do.

 

[ray147]

sql syntax sorted

hi,

issue sorted out, it's working fine now...i did some IF statements to each of the variables and updated a new text string with the '#' or else a NULL value if the field is empty... below is an excerpt of the code FYI:

If PlanDespTime.Text <> "" Then
time1 = "#" & PlanDespTime.Text & "#"
Else
time1 = "NULL"
End If

Thanks for yr help.