Forcing access to use english date

[Steven Scaife]

I have the follwoing sql query that i am running through my VBA code but it
returns no results even tho its right

SELECT Time_Of_Appointment, Postcode FROM Appointment WHERE (Agent_ID = 20)
AND (Date_Of_Travel = #5/11/2004#)

When i paste this into the SQL screen and view it it returns no results when
i go back to design view its changed the criteria to

#11/05/2004#

It was originally using the us date when i viewed it in object browser so i
rebuilt the date using day()month()year() to get the date i needed

Any one have any ideas

in ASP i would set the locale is there a similar thing in Access, i thought
it would just use the system locale which is set to UK, but obviously not

thanks

 

[Rick B]

What is the date format for the computer you are using? Go to the control
panel and look at the regional options. My guess (and only a guess) is that
this is where it is getting that format from.

Rick B

 

[John Smith]

Access has a preference for US dates and will use them whenever it can, it also
expects SQL dates to be in US form regardless of your regional settings.

To prevent it from doing this I always format dates as d/mmm/yyyy which gives
it no leeway for rearranging them. For your query this would be something like
:-

SQL = "SELECT Time_Of_Appointment, Postcode FROM Appointment" _
& " WHERE Agent_ID = 20 AND Date_Of_Travel = #" _
& Format(SomeDateField, "d/mmm/yyyy") & "#"

 

[Steven Scaife]

I had thats how i knew it wasnt using the system locale. Its getting on my
nerves now all i need to do is crack this then i can start on a new project.

 

[Tony Wainwright]

When using code Access reverts to US date format. Try:

SELECT Time_Of_Appointment, Postcode FROM Appointment WHERE (Agent_ID = 20)
AND (Date_Of_Travel = Format(#5/11/2004#, "dd/mm/yyyy"))


Tony

 

[Allen Browne]

For more information, based on 10 years working with Access in a dd/mm/yyyy
country, see:
International Date Formats in Access
at:
http://members.iinet.net.au/~allenbrowne/ser-36.html

 

[Steven Scaife]

You're a star cheers mate

Access has a preference for US dates and will use them whenever it can, it also
expects SQL dates to be in US form regardless of your regional settings.

To prevent it from doing this I always format dates as d/mmm/yyyy which gives
it no leeway for rearranging them. For your query this would be something like
:-

SQL = "SELECT Time_Of_Appointment, Postcode FROM Appointment" _
& " WHERE Agent_ID = 20 AND Date_Of_Travel = #" _
& Format(SomeDateField, "d/mmm/yyyy") & "#"

--
HTH
John

not