Date problem - update query keeps converting to 12/30/1899 1

[BofRossClan]

I am coding the way I always have, and formatting my date the same, but suddenly, this date (which is keyed into the field Me![dtDelDate] on my form), is converted to 12/30/1899 when I execute the following code:

Dim dtDelivDate As Date
dtDelivDate = Me![dtDelDate]

DoCmd.RunSQL "UPDATE DISTINCTROW [Market Orders] SET [Market Orders].[Order Date] = " & dtDelivDate & " WHERE ((([Market Orders].Control)='C'));"

No matter what date I put in, it comes out the same wrong date.

Has anyone ever had this happen? What am I missing?

 

[cmmrfrds]

dtDelivDate = Me![dtDelDate]
dtDelivDate = Me.[dtDelDate]

debug.print "date = " dtDelivDate

The field must be getting nulls so it is defaulting. Are you sure that is the right syntax for picking up the text field from the Form - check it out with a debug.

 

[BofRossClan]

The date shows up correctly in the debug window. That's driving me crazy!

date = 03/27/2002

I've tried formatting the date differently to see if it makes a difference. Right now I'm using the mm/dd/yyyy format. I'm using the standard input mask: 99/99/00;0;_
I've tried it with a default and without a default.

I've tried using the form reference directly instead of using a variable. Still the same. I think it's possessed.

 

[cmmrfrds]

Date need to be surrounded by # in Access

SET [Market Orders].[Order Date] = " & "#" & dtDelivDate & "#" & " WHERE ((([Market Orders].Control)='C'));"

 

[BofRossClan]

That's it! Thanks. I had tried that, but must have put it in wrong. Access is SUCH a stickler about syntax.