Date Formatting in MS Access

[lancerine]

I hope someone can help. This problem has been really annoying for these 2 months.

I made application in MS Access with linked tables to Oracle Server. The dates in each tables are formatted manually "dd/mm/yyyy" like I wanted. I also has changed the regional settings>date>dd-mm-yyyy.
In a form to submit a data I used unbound textboxes; if it's for date I formatted it with "dd-mm-yyyy".
All data will be inserted into the database with one button which has event procedure to run sql statement:
INSERT INTO mytable VALUES .....

But when the dates submitted i.e. 11-06-2003(means 11 June 2003), It is saved as 06-11-2003 (6 November 2003) as I looked into the table.
I have tried to involved format function in the INSERT STATEMENT:
INSERT INTO mytable VALUES (#" & Format(Me.myDate.Value,"dd/mm/yyyy&quot & "#)"
but that did not work also.

Can someone help me please? I've been trying to search all forums but nothing works yet. Thanks!

 

[nicsin]

Hi lancerine,

try formating the date as "dd-mmm-yyyy". I remember I had the same problem and solved it this way. I hope it works for you too.

 

[lancerine]

Nicsin,
should I format all the dates as "dd-mmm-yyyy" in regional settings or only in Access? (or in the INSERT STATEMENT?)
Thanks..!

 

[nicsin]

In the INSERT statement lancerine. Try it and tell me the results.

regards,
nicsin

 

[lancerine]

I inserted format dd-mmm-yyyy in all insert statements, and it works now
Thanks a lot nicsin..!
Access is quite weird...

 

[nicsin]

Access is an american product and whatever format you present to it, inside it always stores it as m d y. From my experience it only understands the name of the month so it will obey our orders ;-)

Have fun!