Date/Time help

  • Thread starter Thread starter Michael Anderson
  • Start date Start date
M

Michael Anderson

I am trying to create queries based on a General Date/Time field I
have. The field contains both date and time in "MM/DD/YYYY HH:MM
am/pm" format.

Some of the queries I would like to do is to list records for today
and yesterday, list records for the current hour, past hour, previous
hour, and previous 24 hours.

I have not been able to find any information on the net as to how to
do this. Could anyone please provide assistance?

Thank you.
 
Add a calculated fields to the query that is based on the table:
DateOnly: DateValue([DateTimeFieldName])

Then use criteria expressions similar to this with the above calculated
field or with the original date/time field:
Between DateAdd("yyyy", -1, Date()) And Date()
Between DateAdd("h", -1, Now()) And Now()

Variations on the above themes can be done to fit your circumstances, but
this should get you started in the right direction.
 
Ken,

Thank you for the tip. It really helped!


Now that you helped me get that part done, I was trying to get all the
records for the entire day before. If I use:

DateAdd("d",-1,Date())

I don't get anything back. Using:

DateAdd("d",-1,Now())

Will only give me yesterday's messages for the same time.

Another thing I"m trying to do is get a record count based on date or
hour and date. Any ideas?
 
DateAdd("d",-1,Date())

I don't get anything back.
Click to expand...

Try typing Ctrl-G to open the VBA window and type

?Date

in the Immediate window. Do you get today's date? an error message? or
nothing at all?

If either of the latter, it appears to be the very common References
bug. Open any module in design view, or open the VBA editor by typing
Ctrl-G. Select Tools... References from the menu. One of the
..DLL files required by Access will probably be marked
MISSING. Uncheck it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.
 
In addition to John's answer, if you're trying to use that criterion
expression on a date/time field that is storing time values as well, it
won't work usually except for times of midnight. Be sure that you're using
the expression on the calculated field that uses DateValue function.

--

Ken Snell
<MS ACCESS MVP>

Michael Anderson said:
Ken,

Thank you for the tip. It really helped!


Now that you helped me get that part done, I was trying to get all the
records for the entire day before. If I use:

DateAdd("d",-1,Date())

I don't get anything back. Using:

DateAdd("d",-1,Now())

Will only give me yesterday's messages for the same time.

Another thing I"m trying to do is get a record count based on date or
hour and date. Any ideas?


Add a calculated fields to the query that is based on the table:
DateOnly: DateValue([DateTimeFieldName])

Then use criteria expressions similar to this with the above calculated
field or with the original date/time field:
Between DateAdd("yyyy", -1, Date()) And Date()
Between DateAdd("h", -1, Now()) And Now()

Variations on the above themes can be done to fit your circumstances, but
this should get you started in the right direction.
Click to expand...
Click to expand...
 
Back
Top