VBA SQL Update string to update table using Now Function

[chris88]

hi,

Im using a SQL string to update several columns within my table, one of whcih needs to be now() but i cant get it working.

the sql string is:
qs = "UPDATE [Holidays] SET Cancelled_By = '" & Me.User_iD & "', Date_Cancelled = " & Now & ",

which throws the error:
Runtime Error 3075
syntax error (misisng operator) in query expression

im guesisng its something to do with teh format of it but cant fidn anything on google or this forum that helps me with this specific issue.

Thanks in advance

 

[NauticalGent]

Hi chris88,

I would suggest you let Access do the heavy lifting and use an Update query. Design it, run it and once it is working fine, you can always switch to SQL view and do a cut and paste. However, it has been my experience that stored queries run more efficiently than running SQL strings.

For a small task such as this you probably would not notice any difference.

Good luck, if you need help with the query just ask

John

 

[CJ_London]

you have a superfluous comma at the end of your string. And Now should be Now()

Also, when passing dates as a string ensure they are in the US format of mm/dd/yyyy

 

[chris88]

Hi John,

thanks for the reply. I've dont realy want to use a query to do it but used your suggestion and did an update query first then used teh code form that.

brings me to the issue of formatting.........the update query doesnt give the syntax required to ensure it udpates with an sql string in vba. i know that when using dates in this way you have to incorperate # either side of teh date value but for the life of me i cant figure out exactly where they need to go and im now getting a dat type mismatch error.

vba string:
qs = "UPDATE [Holidays] SET Cancelled_By = '" & Me.User_iD & "', Date_Cancelled = Now(), Agreed_Or_Declined = '" & "Yes" & "' WHERE Employee_Id = '" & Me.SearchRef & "'AND Holiday_Type = '" & Me.Holiday_Type & "'AND Date_Of_Holiday = '" & Me.Date_Of_Holiday & "';"

its specifically the Date_Cancelled = Now(), bit causing my issue.

thanks

 

[NauticalGent]

chris88,

the "#" are not needed in this case because you are not passing a literal. Did you follow CJ's advice regarding the format? The default it the US standard of mm/dd/yyyy vice what the rest of the world's which is dd/mm/yyyy.

 

[chris88]

UPDATE

qs = "UPDATE [Holidays] SET Cancelled_By = '" & Me.User_iD & "', Date_Cancelled = #" & Now() & "#, Agreed_Or_Declined = '" & "Yes" & "' WHERE Employee_Id = '" & Me.SearchRef & "'AND Holiday_Type = '" & Me.Holiday_Type & "'AND Date_Of_Holiday = #" & Me.Date_Of_Holiday & "#;"

doest give me any errors at all but doesnt actually update the table either

 

[NauticalGent]

When you do a Update query, do you get the intended results?

 

[CJ_London]

this was right before

Date_Cancelled = #" & Now() & "#, Agreed_Or_Declined

should be

Date_Cancelled = Now(), Agreed_Or_Declined

this is passing a text value to what I would expect to be a boolean

Agreed_Or_Declined = '" & "Yes" & "' WHERE

should be

Agreed_Or_Declined = True WHERE

you are missing a space before the AND here

& "'AND Holiday_Type

& "'AND Date_Of_Holiday

and this (if you are not in the US) should be either

Date_Of_Holiday =#" & format(Me.Date_Of_Holiday,","mm/dd/yyyy") & "#;"

or

Date_Of_Holiday =CDate(" & Me.Date_Of_Holiday & ");"

I also assume that EmployeeID and HolidayType are both text in your table

After the line creating the qs string put

debug.print qs

which will show the string in the immediate window

then compare that with the sql produced by your query

 

[chris88]

sorry im in and out of meetings and this network connection is shocking so missed CJ's initial post Post.

Yes CJ EmployeeID and holiday type are both text and that explains it a lot easier for me.

followed it through and i now get an expectec list seperator or ) error on

format(Me.Date_Of_Holiday,","mm/dd/yyyy") - specifically with the mm

No errors when i use

"' AND Date_Of_Holiday =CDate(" & Me.Date_Of_Holiday & ");"

however it doesnt actually update the table as expected

thanks both, really appreciate the help on this

 

[chris88]

SOLVED

changed

"' AND Date_Of_Holiday =#" & format(Me.Date_Of_Holiday,","mm/dd/yyyy") & "#;"

to

"' AND Date_Of_Holiday = #" & Format(Me.Date_Of_Holiday, "mm/dd/yyyy") & "#;"

and it works perfectly

Thanks again CJ

 

[CJ_London]

sorry, my mistake with copy and pasting