dates as query criteria

[Doug F.]

On the query grid for a date field I can hardcode #5/7/2006# but find that
using #may/7/2006# is more reliable as it's specific and unambiguous, ie is
it May 7 or July 5 that I want. Does the local machine setting do the
determination when using the #5/7/2006# format? Is my #may/7/2006# solution
commonly used?

Also, I believe that I must use the #...# to indicate a date, true? When I
use the between [date1] and [date2] construct as a user prompt must I use ##
somehow?
Thanks.

 

[Allen Browne]

The # delimiter is correct for literal dates within the SQL statement (and
in VBA also.)

When adding paramters to a query, do not add the # to the parameter name.

Internally, Access actually stores dates as real numbers, where the integer
part represents the date and the fraction part the time. You can see this
for yourself if you open the Immediate window (Ctrl+G) and enter:
? CDbl(Now())

Using text (e.g. may) inside the # is not correct.

For details on how Access interprets date entries, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

 

[Rob G]

Great post Allen. I went to your website and you helped me resolve a real
nagging date problem that I had in my where clause. SQLDate did the trick.

The # delimiter is correct for literal dates within the SQL statement (and
in VBA also.)

When adding paramters to a query, do not add the # to the parameter name.

Internally, Access actually stores dates as real numbers, where the integer
part represents the date and the fraction part the time. You can see this
for yourself if you open the Immediate window (Ctrl+G) and enter:
? CDbl(Now())

Using text (e.g. may) inside the # is not correct.

For details on how Access interprets date entries, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

On the query grid for a date field I can hardcode #5/7/2006# but find that
using #may/7/2006# is more reliable as it's specific and unambiguous, ie
is
it May 7 or July 5 that I want. Does the local machine setting do the
determination when using the #5/7/2006# format? Is my #may/7/2006#
solution
commonly used?

Also, I believe that I must use the #...# to indicate a date, true? When
I
use the between [date1] and [date2] construct as a user prompt must I use
##
somehow?
Thanks.

 

[Doug F.]

Thanks muchly.
--
Doug F.

The # delimiter is correct for literal dates within the SQL statement (and
in VBA also.)

When adding paramters to a query, do not add the # to the parameter name.

Internally, Access actually stores dates as real numbers, where the integer
part represents the date and the fraction part the time. You can see this
for yourself if you open the Immediate window (Ctrl+G) and enter:
? CDbl(Now())

Using text (e.g. may) inside the # is not correct.

For details on how Access interprets date entries, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

On the query grid for a date field I can hardcode #5/7/2006# but find that
using #may/7/2006# is more reliable as it's specific and unambiguous, ie
is
it May 7 or July 5 that I want. Does the local machine setting do the
determination when using the #5/7/2006# format? Is my #may/7/2006#
solution
commonly used?

Also, I believe that I must use the #...# to indicate a date, true? When
I
use the between [date1] and [date2] construct as a user prompt must I use
##
somehow?
Thanks.