Updating date in table produces bizarre results

[fredalina]

i have the following code to update 2 fields in a table with 2 dates. One is the day the report is run (today), the other is the value of the variable "lastday" (the value of which is last Friday, 1/2/2009 in U.S. notation, 2/1/2009 in European notation; i'm in the U.S. so will use U.S. notation from here out, thanks).

                    MsgBox "lastday is " & lastday
                    MsgBox "date is " & Format(Date, "mm/dd/yyyy")
                    
                    sqlDate = "UPDATE [KPI Update] SET [KPILastDate]=" & lastday & " WHERE Index = 1"
                    DoCmd.RunSQL sqlDate
                    sqlDate = "UPDATE [KPI Update] SET [KPIUpdateDate]=" & Format(Date, "mm/dd/yyyy") & " WHERE Index = 1"
                    DoCmd.RunSQL sqlDate

The 2 MsgBoxes in the beginning are to hopefully verify that i'm not insane. The results of the MsgBoxes are "lastday is 1/2/2009" and "date is 1/9/2009", which are correct.

However, i cannot get the results in the table to come out correctly. i've tried making the fields in the table be date fields, and changing the format to Short Date, Long Date, Medium Date, just about everything. i've tried making them text fields with or without an input mask.

i get results everywhere from "12/30/1899" (yes, 1899!) to scientific notation to "12:10 AM" without an actual date. Since my code earlier in the procedure does a lookup on those two fields to determine if we need to continue (i.e. is the KPIUpdateDate more than a week old), i need these fields to be accurate.

Help?

Thanks!

 

[Atomic Shrimp]

What type of variable is lastday?

 

[fredalina]

Sorry. The variable lastday is variant at the moment. I did try it as Date and String, with the same results.

Thanks!

 

[Vonnie]

If it is a date field, doesn't this work? missing the #?

sqlDate = "UPDATE [KPI Update] SET [KPILastDate]=#" & lastday & "# WHERE Index = 1"

 

[fredalina]

If it is a date field, doesn't this work? missing the #?

sqlDate = "UPDATE [KPI Update] SET [KPILastDate]=#" & lastday & "# WHERE Index = 1"

Yes, it works perfectly. i figured it was probably a syntax issue.

Thanks so much for getting me back in the 21st century!