insert date in correct format, keeps swapping day month

[Wozzzzza]

i have a table set as date/time format, format as dd/mm/yyyy, windows is also set to the same date format.
i have a query that inserts todays date in this table field using # & Date & # type of format.
when run it inserts the date as mm/dd/yyyy so say its 1st march 2010 it will insert the date as 3rd of january until such time the date passes the 12th of a month.
when ever i run a query to extract monthly figures it treats march 1st as january, march 2nd as february and so on.
i have tried formatting the inserted date as dd/mm/yyyy but still it twists the inserted date around.
how do i get it to insert a date like 1st march 2010 as that and not a january date??

 

[Galaxiom]

The date format in pure SQL is always #MM/DD/YYYY#. When using VBA RunCommand or RunSQL you must use this format.

However the Access query designer displays #DD/MM/YYYY# in DD/MM/YYYY systems.

It also tries to be clever. When confronted with an invalid date in the sytem format it will quietly accept it in the other format if it is valid. Personally I believe this is more likely to lead to data errors than ever be of real use.

 

[Wozzzzza]

yep that fixed it, dunno how on earth i have never come across that problem before with all the years ive been using access.

 

[Galaxiom]

dunno how on earth i have never come across that problem before

Access is so diverse that few developers have experienced anything like the full gambit of its capabilities. We all have our specialties and insights gained by random experience. It is this collective knowledge that makes this site so productive for all of us.