Storing Dates and Time

[Jeff Freilich]

Hi All

I know there have been other posts on this that I have been reading up
on but I have a couple of questions:

I normally like to use default value on my date fields as Date() as
opposed to Now() as it simply stores the date - which makes it much
easier to Query a specific date or date range - "Between Jan 1, 2009
and Feb 1, 2009"

I also want to store the time and my first thought was to create a new
field "MyTime" to store the time - that would enable me to continue to
use my current MyDate field the same way I have been - but the more I
read (which makes sense) it is not proper to store 2 fields of what
amounts to the same information (My Time would also be storing the
date information as well)

So while I see that it is best to use Now() and store all information
(Date and Time) in the same field - I have a couple of versy specific
questions:

How can I query the records to only find records added on a given date
when Now() is used instead of Date? - Do I have to somehow parse out
only the date - or can I query for just the date - when I use "Between
Feb 6 and Feb 6" it does not pick up the values with that date

Any thoughts or help is appreciated

Thanks,

Jeff

 

[fredg]

Hi All

I know there have been other posts on this that I have been reading up
on but I have a couple of questions:

I normally like to use default value on my date fields as Date() as
opposed to Now() as it simply stores the date - which makes it much
easier to Query a specific date or date range - "Between Jan 1, 2009
and Feb 1, 2009"

I also want to store the time and my first thought was to create a new
field "MyTime" to store the time - that would enable me to continue to
use my current MyDate field the same way I have been - but the more I
read (which makes sense) it is not proper to store 2 fields of what
amounts to the same information (My Time would also be storing the
date information as well)

So while I see that it is best to use Now() and store all information
(Date and Time) in the same field - I have a couple of versy specific
questions:

How can I query the records to only find records added on a given date
when Now() is used instead of Date? - Do I have to somehow parse out
only the date - or can I query for just the date - when I use "Between
Feb 6 and Feb 6" it does not pick up the values with that date

Any thoughts or help is appreciated

Thanks,

Jeff

If the [DateField] includes a Time value, all you need do is add 1 day
to the End date:
Between [Start Date] & DateAdd("d",1,[EndDate])

If you write the criteria on the [DateField] exactly as above, Access
will prompt for the Start and End dates. Remember, dates must be
entered in the U.S. date format of
month day year
or in an unambiguous format, such as 3 Jan 2009.

 

[John W. Vinson]

So while I see that it is best to use Now() and store all information
(Date and Time) in the same field - I have a couple of versy specific
questions:

How can I query the records to only find records added on a given date
when Now() is used instead of Date? - Do I have to somehow parse out
only the date - or can I query for just the date - when I use "Between
Feb 6 and Feb 6" it does not pick up the values with that date

I'd really recommend storing the date and time in the same field. You need to
get all the records from midnight at the start of the day, to the instant
before midnight at the end of the day; you can do this for a single date with
a criterion

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])

Note that it's less than, not less than or equal, so you don't pick up a
record with exactly midnight on February 7.

For a date range you just need to do the same trick with the end date, to pick
up records on the last day:

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

In practice I'd always use form references rather than parameter prompts, but
the principle is the same.