Date not saved correctly!

[TimTDP]

On a form I have an unbound control called dteOrderDate
It's format is set to Long Date

When I run the following code:

DoCmd.RunSQL ("Update tblTmpSupplierOrderAllDetails set PurchaseOrderDate = " & Me.dteInvoiceDate)

the date is stored incorrectly. It is stored as a time! for example, 6/9/12 is stored as 01:20:00 AM!

If I try and format the date like this:
DoCmd.RunSQL ("Update tblTmpSupplierOrderAllDetails set PurchaseOrderDate = " & Format(Me.dteInvoiceDate, "dd/mm/yyyy"))

the date is stored as 12:00:29 AM!

tblTmpSupplierOrderAllDetails!PurchaseOrderDate is a date/time field. No format.


What am I doing wrong?

 

[DJkarl]

On a form I have an unbound control called dteOrderDate
It's format is set to Long Date

When I run the following code:

DoCmd.RunSQL ("Update tblTmpSupplierOrderAllDetails set PurchaseOrderDate = " & Me.dteInvoiceDate)

the date is stored incorrectly. It is stored as a time! for example, 6/9/12 is stored as 01:20:00 AM!

If I try and format the date like this:
DoCmd.RunSQL ("Update tblTmpSupplierOrderAllDetails set PurchaseOrderDate = " & Format(Me.dteInvoiceDate, "dd/mm/yyyy"))

the date is stored as 12:00:29 AM!

tblTmpSupplierOrderAllDetails!PurchaseOrderDate is a date/time field. No format.


What am I doing wrong?

In access / vba dates need to be encapsulated with ##,
try

DoCmd.RunSQL ("Update tblTmpSupplierOrderAllDetails set PurchaseOrderDate =#" & Format(Me.dteInvoiceDate, "dd/mm/yyyy") & "#")

 

[TimTDP]

How stupid of me!
Thanks for reminding me to use #

DoCmd.RunSQL ("Update tblTmpSupplierOrderAllDetails set PurchaseOrderDate = #" & Format(Me.dteInvoiceDate, "dd/mm/yyyy") & "#")

Access now stores 6/9/12 as 9/6/12! 6/9/12 is the correct date (6 September 2012)

 

[JANR]

Access use US date formats mm/dd/yyyy so yor format() function is incorrect.

DoCmd.RunSQL ("Update tblTmpSupplierOrderAllDetails set PurchaseOrderDate = #" & Format(Me.dteInvoiceDate, [COLOR="Red"]"mm/dd/yyyy[/COLOR]") & "#")

How ever since you have formatted your control dteInvoiceDate as a Date and you are using RunSQL you can just use the full form refrence to the control and Access will validate your regional settings on your computer. Just match what's marked in red with the correct formname

DoCmd.RunSQL ("Update tblTmpSupplierOrderAllDetails set PurchaseOrderDate = Forms![COLOR="Red"]NameOfForm[/COLOR]!dteInvoiceDate")

But my prefrence is to use .Execute methode to avoid Access warnings messages.

Currentdb.Execute "Update tblTmpSupplierOrderAllDetails set PurchaseOrderDate = " & Format(Me.dteInvoiceDate, "\#mm\/dd\/yyyy\#", dbFailOnError

One question thou why havent you included a Where-clause, the way your update statement reads it will update ALL records in your table.

JR