Thread: Update not working
12-06-11, 13:08 #1Registered User
- Join Date
- Nov 2011
Unanswered: Update not working
I have the following code that updates a table after it inserts.
DoCmd****nSQL "UPDATE promise_date_changes SET [ReasonCode] = '" & ReasonCodeVar & "', [FullDescription] = '" & CodeDescriptionVar & "' " & _
"WHERE [WO#] = " & WO2 & " AND PRCMPY = '" & PRCMPY & "' AND [PRORD#] = " & PRORD & " AND Ship = " & CDate(#12/31/1900# + NewDateVar) & " "
However the update is not working I believe it is something to do with the date field, I am using access and the field that ship is in is a date field with a short date format, however when I turned the date field to a text field and the field into a date field it did work.
What am I doing wrong?
12-06-11, 13:16 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
well first off I suspect posting what looks like Access VBA in MySQL.
I'd expect a column called ReasonCode to be text/string but you know your data
Imn suspicious of the date bit inthe where clause, that looks very fishy to me
assuming it is VBA can you examine what the SQL actually being sent to the SQL engine is (ie variaible names replaced with actual values).
usually when doing date manipultaion in any form of db its best to use the intrisic date functions for that DBI'd rather be riding on the Tiger 800 or the Norton