Using Dates in a query

[Guest]

I have written a query looking at data from the next month using the
following expression - Between Date$() and DateAdd("d",30,Date$()) This
worked fine for about 3 weeks but at the end of February the displayed date
suddenly switched from English UK Short date format ie dd/mm/yyyy to American
Short date Format ie mm/dd/yyyy. The resulting date 03/01/2006 was a valid
but wrong date as far as the application was concerned as it should have been
01/03/2006.
Access help says that the short date format is set by the default setting
used in windows which is still set to English UK short date.
Does anyone know how to resolve the problem

 

[Marshall Barton]

I have written a query looking at data from the next month using the
following expression - Between Date$() and DateAdd("d",30,Date$()) This
worked fine for about 3 weeks but at the end of February the displayed date
suddenly switched from English UK Short date format ie dd/mm/yyyy to American
Short date Format ie mm/dd/yyyy. The resulting date 03/01/2006 was a valid
but wrong date as far as the application was concerned as it should have been
01/03/2006.
Access help says that the short date format is set by the default setting
used in windows which is still set to English UK short date.
Does anyone know how to resolve the problem

I'm surprised that you got any of that to do something
useful. There are so many date to string to date
conversions involved, I can't even figure out what's going
on.

I think you can get what you want if you use Date(), which
returns a datevalue, instead of Date$(), which returns a
string.

For future referenece. When you do need to convert a date
value to a string that will later be converted back to a
date value, use this: Format(Date(), "\#m\/d\/yyyy#"). The
key thing to remember is that literal date strings must be
enclosed in # signs and must be in either m/d/yyyy or the
unambiguous format yyyy-mm-dd You do not need this in this
case because you should be dealing with Date values, not
strings that look like a date.

 

[John Vinson]

I have written a query looking at data from the next month using the
following expression - Between Date$() and DateAdd("d",30,Date$()) This
worked fine for about 3 weeks but at the end of February the displayed date
suddenly switched from English UK Short date format ie dd/mm/yyyy to American
Short date Format ie mm/dd/yyyy. The resulting date 03/01/2006 was a valid
but wrong date as far as the application was concerned as it should have been
01/03/2006.
Access help says that the short date format is set by the default setting
used in windows which is still set to English UK short date.
Does anyone know how to resolve the problem

Date literals in Access must ALWAYS be in either American mm/dd/yyyy
format, or in an unambiguous format such as dd-mmm-yyyy. I think the
problem is that you are using the Date$() function which returns a
*text string*, rather than the Date() function which returns a
date/time value (actually a double float number).

Between Date() and DateAdd("d", 30, Date())

should work as a criterion regardless of your computer's date format
setting, since the function will return a numeric date/time rather
than a text string which must be secondarily converted.

John W. Vinson[MVP]

 

[Bob Barrows [MVP]]

Date literals in Access must ALWAYS be in either American mm/dd/yyyy
format, or in an unambiguous format such as dd-mmm-yyyy.

Better yet: yyyy-mm-dd

The mmm portion may vary depending on the locale of the machine ...

Bob Barrows

 

[Guest]

Dropping the $ and using Date() seems to have resolved the problem
Thanks