Solved: silly update query error

[OTWarrior]

I have no idea how this is, and it is such a silly small error I am almost embarrassed to ask, but here goes...

I am trying to use an update query to change a date field to a different value and make a boolean false. When I try the update query from the code, it inputs a value of 13/12/1899 (00:00:04 if you actually click in a text box looking into this field). In the testing the date I am entering is 01/12/09

Here is the code and the sql string:



Dim PlanDate As Date
Dim sql As String

PlanDate = Format(InputBox("Please enter the date that the original Plan will finish" & vbCrLf & "in the format dd/mm/yy or dd/mm/yyyy", "SPiTS ~ Prompt"), "Short Date")

sql = "UPDATE tbl_DRPT_DirectPaymentsRecord SET tbl_DRPT_DirectPaymentsRecord.dteDPEndDate = " & Format(PlanDate, "Short Date") & ", tbl_DRPT_DirectPaymentsRecord.boolDPUFN = False " & _
"WHERE (((tbl_DRPT_DirectPaymentsRecord.anDirectPaymentsID)=[forms]![frm_DRPT_LinkSupportPlansToServices]![umbDirectPaymentsID]));"

DoCmd.RunSQL sql

PS: I know it's formatted twice, but I am literally trying anything I can think of to correct it. I have tried it without formatting, and with formatting on each of the sections.

[OTWarrior]

Someone slap me. As soon as I posted it, realised I had forgetten the # next to the actual date fields.

Can't delete the thread, but maybe this will help someone in the future with the same problem.

....Feel really silly now, lol.

[geekgirlau]

OT keep in mind that dates in all MS products are very US-centric, even though you can display them in other formats.

When using a date variable in a SQL string, you need to use a mm/dd/yyyy format.


Dim PlanDate As Date
Dim sql As String


PlanDate = Format(InputBox("Please enter the date that the original Plan will finish" & vbCrLf & _
"in the format dd/mm/yy or dd/mm/yyyy", "SPiTS ~ Prompt"), "Short Date")

sql = "UPDATE tbl_DRPT_DirectPaymentsRecord " & _
"SET dteDPEndDate = #" & Format(PlanDate, "mm/dd/yyyy") & "#, boolDPUFN = False " & _
"WHERE anDirectPaymentsID=[forms]![frm_DRPT_LinkSupportPlansToServices]![umbDirectPaymentsID]"

DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True