Update Date field in table

[jfrizelle]

Hi, I'm trying to update a date field in a table when a user enters a date on a form. However, the date in the table is being updated incorrectly. I enter 05/Jan/04 on the form, and the date in the table shows up as 01/May/04 - the day and month are being switched around.

Here's the code I'm using :

DoCmd.RunSQL "UPDATE tbl_Employee " & _
"SET tbl_Employee.E_Date_Dealer = #" & Me!Date_Moved & _
"# WHERE tbl_Employee.E_Id = " & Me!E_Id & ";"

The date field on the form and the date field in the table are both Long format. Please help - I've been going around in circles with this all afternoon!

Thanks
Jennie.

 

[dyarwood]

Looks like you have the format of the date wrong. Can you reformat the table so it will show UK date format?

 

[jrbarnett]

Jennie
Go into control panel regional settings and check what the system locale is set correctly for your geographical location, as is your timezone in the date/time setting.
These control panel settings are used as defaults for Access if a specific date/time format is not specified.

John

 

[jfrizelle]

Thanks folks. I've checked Regional Options in Control Panel, Short and Long dates are set to dd/MM/yyyy and dd MMMM yyyy. My TimeZone is set to GMT.

In the table tbl_Employee, the E_Date_Moved field is Data Type Date/Time, Format Long, and the table that the form is built on also stores Date_Moved as Data Type Date/Time, Format Long. I've also checked that the field being displayed on the form is format Long Date.

 

[jfrizelle]

John / Dyarwood - I've got it. I changed the code to explicitly format the date field on the form as I'm copying it to the Employees table.

DoCmd.RunSQL "UPDATE tbl_Employee " & _
"SET tbl_Employee.E_Date_Dealer = #" & Format(Me!Date_Moved, "Medium Date&quot & _
"# WHERE tbl_Employee.E_Id = " & Me!E_Id & ";"

Doh, why didn't I do that hours ago??

Thanks again,
Jennie.