Click here to Skip to main content
15,939,065 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have an Access 2007 (.accdb) with a table that contains a date field.
if, using the access Query, I run :
SQL
UPDATE Testx SET [Testx_Date] = #03/04/2013# where Testx_Criteria = 'AA'


I end up with the value 04/03/2013 in the database.

The input is intended to be dd/mm/yyyy but the result in the database is mm/dd/yyyy.
(as discovered when the data is read by a .net or vba application.

I have tried changing the format and input mask in the table (under design view) but to no avail.

Important to note that my OS (WIN 7) Region and language shortdate settings have been set to dd/MM/yyyy.

Can anyone advise me on how to get the date into the table in the same structure as i have it in the SQL statement.

the SQL statement is an abriged version of the real table but tests exatly the same.

URGENT HELP NEEDED PLEEEAAASE.

Thanks guys
Posted
Updated 5-Feb-13 2:40am
v2
Comments
Zoltán Zörgő 5-Feb-13 8:57am    
I don't get your problem. Let Access store it's date field as it wants. You can still format you output as needed. You want to feed in date value in different format as it accepts? Make a VBA function to translate if needed.
Richard MacCutchan 5-Feb-13 9:00am    
This looks like a duplicate of your other question.
Darrell de Wet 5-Feb-13 9:09am    
Hi Richard
Essentially it is. The other question seemed a little .net orientated and i figured that the problem coud be a purely Access problem so i posted again.
I hope that it has not irked anyone.
Either way I have received some good advice on both.

There is a difference between the date stored in the database and the date shown in the user interface.
You think you are inserting the date in this format dd/mm/yyyy, but the settings on your system indicate it is mm/dd/yyyy. To test this, just try 26/02/2013 and you'll get an error.

To overcome this, there is a very simple trick. Always start with the year first. The format yyyy/mm/dd is always interpreted correctly. Give it a try.

Good luck!
 
Share this answer
 
If you able to change table structure you should use custom date/time format then.
Please refer this article:
http://office.microsoft.com/en-us/access-help/format-the-date-and-time-field-in-access-HA010341474.aspx[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900