MS Access Date field formatting

Question

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 5-Feb-13 2:38am

Darrell de Wet

Updated 5-Feb-13 2:40am

ZurdoDev

v2

Add a Solution

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.

2 solutions

Solution 2

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[^]

Posted 5-Feb-13 3:16am

skydger

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

E.F. Nijboer · Accepted Answer · 2013-02-05T03:04:00

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!