Forcing access to use english date

  • Thread starter Thread starter Steven Scaife
  • Start date Start date
S

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
 
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
 
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") & "#"
 
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.
 
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
 
You're a star cheers mate

John Smith said:
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
Click to expand...
 
Back
Top