date/time in query, access switching dateformat? 2

[mrmovie]

i think i have gone off access pretty quickly.
i have the following behavour.

SELECT WHERE Header.FromDate > #13/03/2005#

gives the correct results

but the following

SELECT WHERE Header.FromDate > #01/04/2005#

behaves as if it is meaning 4th January!!!
my regional setting are
25/04/2005 settings.

why is access switching back and forth like this? and how do i stop it?

thanks
rich

 

[Golom]

Access always internally uses MM/DD/YYYY as its date format regardless of your localle settings. Change it to something that's unambiguous like

SELECT WHERE Header.FromDate > #01/Apr/2005#

 

[mrmovie]

to get round this apparent date hell should i just be converting all my dates to the integral values before chucking it in the SQL? if so is there a built in method for this?
i am happy to convert dates/strings using month() day() year() or whatever but this doesnt seem to help me out at all. i need to tell access, look forget about being clever here is the number of days since 1927 or whenever the flippin day 1 is!! sorry this has taken me all day to get this far and i thought it would be a 5 minute job

rich

 

[mrmovie]

thanks, woods and trees moment there born out of frustration and a stinking hangover!

 

[PHV]

To get rid of any regional setting issue use the ANSI like date format:
#yyyy-mm-dd#

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Golom]

Your dates ARE already in numeric format. Access stores dates as a type double. mm/dd/yyyy or dd/mm/yyyy are representational decisions about how the dates are displayed but they don't affect the way that they are stored.

You can do things like

SELECT WHERE CDbl(Header.FromDate) > 38467

but that means that you will somewhere need to do the calculation to determine that today (for example) is day number 38467. For a generic solution to this you can use DateSerial as in

SELECT WHERE Header.FromDate > DateSerial (2005, 04, 01)

 

[mrmovie]

thanks PHV