Querying Access using the UK date format

[monkeymagic2222]

Hi,

I am using ASP to query a Microsoft Access database.

I have created a form, which allows a user to enter a start date and an end date, in the standard European format of dd/mm/yyyy. The ASP then queries my database to pull out any entries that are greater than the start date and less than the end date.

The problem I have is that Access is assuming my query is in American format, mm/dd/yyyy and so displaying the wrong result e.g. I enter my start date as 01/04/2003 (which should be 1st April 2003) and Access thinks I mean 4th January 2003)

I can imagine that this has been raised many times before, is there an easy work around, without forcing the users to use the wrong date format?

A few sites have suggested adding Session.LCID = 2057 to the global.asa file or failing that, the actual web page but this seems to achieve nothing???

 

[krieghof]

Had same problem before, On entering the date of the user why dont you parse it to the correct format? i know Acccess prefares its on date object when evaluating.

 

[GaryC123]

You can query the database with the general data format i.e. yyyy/mm/dd
Then whatever way dates are stored in your DB you will get the right records returned, assuming you've set the field in the DB to the format your using ;o)

http://www.intouch-media.co.uk

 

[JamesLean]

The easiest way is to have 3 seperate inputs for day, month and year on your form (I use selects but you could use text boxes if you like). Then you can concatenate these into a non-ambiguous format, eg yyyymmdd, before querying the db.

I think it's always safest to use this method as you don't have to rely on regional settings being correct etc.. if you have to change you server or anything.

--James

 

[monkeymagic2222]

Thanks all for your replies, I think my easiest option is to go for the 3 seperate input boxes and then concatenate them into US format.