Re: Date vs DateTime values
- From: John Vinson <jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 09 Nov 2006 00:00:05 -0700
On 8 Nov 2006 20:27:28 -0800, "rpboll" <RPBOLL@xxxxxxxxx> wrote:
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]
.
- References:
- Date vs DateTime values
- From: rpboll
- Date vs DateTime values
- Prev by Date: Re: Allowing new records to be added into a query
- Next by Date: Re: Allowing new records to be added into a query
- Previous by thread: Re: Date vs DateTime values
- Next by thread: Re: Date vs DateTime values
- Index(es):
Relevant Pages
|
