Date Field Updates correctly manually & in query grid, but not VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using the following VBA code to update the date field of one or more
records in a subform.

Dim datNewCompleteDate as date
datNewCompleteDate = #12/12/2004#
DoCmd.RunSQL "UPDATE genApps SET genApps.CompleteDate = " & datNewCompleteDate

This code updates the date field to 12/30/1889.

And upon putting my cursor in the field it reveals--12:00:43 AM.

The field properties in both the underlying table and the subform are
"mm/dd/yy", 99/99/00;;_

Attempting to use the 4-digit date feature under the Tools, Option, General,
along with a different format like "Short Date" doesn't help either.

However, the correct date does appear if I either enter the date manually or
use the regular query window to update the records.

The only time it doesn't work is with this VBA statement.

Any help is appreciated.

Please reply to (e-mail address removed) if you can.

Thanks,
Michael
 
Access101 said:
I'm using the following VBA code to update the date field of one or more
records in a subform.

Dim datNewCompleteDate as date
datNewCompleteDate = #12/12/2004#
DoCmd.RunSQL "UPDATE genApps SET genApps.CompleteDate = " &
datNewCompleteDate

This code updates the date field to 12/30/1889.

And upon putting my cursor in the field it reveals--12:00:43 AM.

The field properties in both the underlying table and the subform are
"mm/dd/yy", 99/99/00;;_

Attempting to use the 4-digit date feature under the Tools, Option,
General,
along with a different format like "Short Date" doesn't help either.

However, the correct date does appear if I either enter the date manually
or
use the regular query window to update the records.

The only time it doesn't work is with this VBA statement.

Any help is appreciated.

Please reply to (e-mail address removed) if you can.

Thanks,
Michael
Click to expand...

Michael,

Try this instead:

DoCmd.RunSQL "UPDATE genApps SET genApps.CompleteDate = #" &
datNewCompleteDate & "#"
 
Back
Top