Hi Guys

I have a text box on a form that displays a date (e.g 02 Oct 2003)

I run a procedure which ioncludes the line:

strSQL = strSQL & " AND ((MasterDatabase.Field24)<=#" & Me.TxtLapsedDate + Me.TxtPreLapsedDays & "#);"

When I look at the query in design mode - the date has miraculously changed to #10/03/2003#


AAARRRGGGHHH


Any suggestions?


Cheers


John

What is the date value supposed to be?

The one you've got in there will be read by access as October 3rd 2003, regardless of your regional settings.

-AJ

Hi

I want it to be October the 3rd - but the query reads it as March the 10th


J

Are you sure its reading it as March 10? Date values denoted by the # symbols are read in mm/dd/yyyy format if doing so makes a valid date, regardless of regional settings.

However, the best way to avoid the problem altogether is to force the date into a non-ambiguous format. Try the following code.

strSQL = strSQL & " AND ((MasterDatabase.Field24)<=#" & Format(Me.TxtLapsedDate + Me.TxtPreLapsedDays, "dd-mmm-yyyy") & "#);"

Then the date value in the resulting SQL should be #03-Oct-2003#, which cannot possibly misinterpreted by the database engine.

-AJ

Thanks AJ - I'll give it a try

Cheers


John

Hi AJ

That's worked a treat - many thanks


John

Great! Glad you got it working, and I'm happy to help.

-AJ

Hi John
Just as an added reference about dates in SQL's..
International Dates in Access
May prove useful one day.
Which would come first the earth freezing over or we all get to use the same date format....(I can feel the temperature dropping)

(IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif)

Hi Larry

Thanks very much - looks useful


Cheers


John