Date vs DateTime values

[rpboll]

I appended some Date values into a table with a DateTime field. The
date values are short dates (i.e., 10/15/06)

I entered a few date stamps using the NOW() that include the present
date and the time.

When I execute this query:

=#1/1/2004# And <= #11/8/2006#

only the shortdates are returned. Any suggestions to include all dates
within the range regardless of whether it includes datestamps or not?
Thanks.

RPBoll

 

[Duane Hookom]

All date values can store times. "short date" is simply a format for
displaying and has absolutely nothing to do with how the value is stored. If
you want to compare date parts (without the time) you can wrap your field in
the DateValue() function.

 

[Van T. Dinh]

I assume your current date is Nov 08, 2006 ...

Note that if you use Now() to set the value of your Field, then the value
(most likely) has non-zero time component and this makes the value greater
than #11/08/2006# and therefore not selected by your criteria.

If you have non-zero time component in the values and you want to select by
date only, you should set your criteria to:

= #1/1/2004# And < DateAdd("d", 1, #11/8/2006#)

DateAdd("d", 1, #11/8/2006#) is equal to #11/09/2006 00:00:00#

 

[John Vinson]

I appended some Date values into a table with a DateTime field. The
date values are short dates (i.e., 10/15/06)

I entered a few date stamps using the NOW() that include the present
date and the time.

When I execute this query:
only the shortdates are returned. Any suggestions to include all dates
within the range regardless of whether it includes datestamps or not?
Thanks.

RPBoll

Just to add to Duane and Van's comments: a Date/Time value in Access
is stored as a double float number, a count of days and fractions of a
day (times) since midnight, December 30, 1899. As such ANY date/time
value has a time component - it may often be .0, corresponding to
midnight.

The format only controls how this numeric value is interpreted for
display, not what's stored. If you have 39029.9968171296 stored in
your table field, it corresponds to 11/8/2006 11:55:25 PM - or to
11/8, or to 11:55 PM, or to 23:55:25, or to 8 November 2006, depending
on how you choose to set the Format property of the control displaying
it.

Using Now() as you did to populate a field fills in the current system
clock time and date. If you don't want the time portion, use Date()
instead - it will still fill in a time, as it must, but that time will
be midnight at the start of the day.

To ensure that all date/time values (with or without a nonzero time
portion stored) are retrieved in a query use a criterion such as

= [Enter start date:] AND < DateAdd("d", 1, DateValue([Enter End Date:]))

to get the 24 hours past midnight of the user's entered date included.

John W. Vinson[MVP]

 

[Guest]

An even easier method is to use the DateValue() function.

WHERE DateValue([yourDateField]) BETWEEN #1/1/2004# And #11/8/2006#

--
Email address is not valid.
Please reply to newsgroup only.

I appended some Date values into a table with a DateTime field. The
date values are short dates (i.e., 10/15/06)

I entered a few date stamps using the NOW() that include the present
date and the time.

When I execute this query:
only the shortdates are returned. Any suggestions to include all dates
within the range regardless of whether it includes datestamps or not?
Thanks.

RPBoll

Just to add to Duane and Van's comments: a Date/Time value in Access
is stored as a double float number, a count of days and fractions of a
day (times) since midnight, December 30, 1899. As such ANY date/time
value has a time component - it may often be .0, corresponding to
midnight.

The format only controls how this numeric value is interpreted for
display, not what's stored. If you have 39029.9968171296 stored in
your table field, it corresponds to 11/8/2006 11:55:25 PM - or to
11/8, or to 11:55 PM, or to 23:55:25, or to 8 November 2006, depending
on how you choose to set the Format property of the control displaying
it.

Using Now() as you did to populate a field fills in the current system
clock time and date. If you don't want the time portion, use Date()
instead - it will still fill in a time, as it must, but that time will
be midnight at the start of the day.

To ensure that all date/time values (with or without a nonzero time
portion stored) are retrieved in a query use a criterion such as

= [Enter start date:] AND < DateAdd("d", 1, DateValue([Enter End Date:]))

to get the 24 hours past midnight of the user's entered date included.

John W. Vinson[MVP]

 

[Van T. Dinh]

Dale

It is easier but less efficient, I think, especially for large number of
records. The reason is that the DaleValue() function is executed for each
value in the Table.

In J.V. method, the DateAdd and DateValue is executed only once regardless
of the number of Record. In addition, if the DateTime Field is indexed, JET
/ Rushmore Query timization will be able to use the Index to select the
records.

--
HTH
Van T. Dinh
MVP (Access)

An even easier method is to use the DateValue() function.

WHERE DateValue([yourDateField]) BETWEEN #1/1/2004# And #11/8/2006#

--
Email address is not valid.
Please reply to newsgroup only.

I appended some Date values into a table with a DateTime field. The
date values are short dates (i.e., 10/15/06)

I entered a few date stamps using the NOW() that include the present
date and the time.

When I execute this query:
=#1/1/2004# And <= #11/8/2006#
only the shortdates are returned. Any suggestions to include all dates
within the range regardless of whether it includes datestamps or not?
Thanks.

RPBoll

Just to add to Duane and Van's comments: a Date/Time value in Access
is stored as a double float number, a count of days and fractions of a
day (times) since midnight, December 30, 1899. As such ANY date/time
value has a time component - it may often be .0, corresponding to
midnight.

The format only controls how this numeric value is interpreted for
display, not what's stored. If you have 39029.9968171296 stored in
your table field, it corresponds to 11/8/2006 11:55:25 PM - or to
11/8, or to 11:55 PM, or to 23:55:25, or to 8 November 2006, depending
on how you choose to set the Format property of the control displaying
it.

Using Now() as you did to populate a field fills in the current system
clock time and date. If you don't want the time portion, use Date()
instead - it will still fill in a time, as it must, but that time will
be midnight at the start of the day.

To ensure that all date/time values (with or without a nonzero time
portion stored) are retrieved in a query use a criterion such as

= [Enter start date:] AND < DateAdd("d", 1, DateValue([Enter End
Date:]))

to get the 24 hours past midnight of the user's entered date included.

John W. Vinson[MVP]