in an access query, how do I define 2 hours ago ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

As the title says.

I'm new to database queries and wish to create/run a small set of saved
queries. I was pleased to find information on =DateAdd which I used to check
records for the previous day, but I can't seem to find an equivalent so I can
check records generated in the previous couple of hours ?

Can anyone suggest what I need to type into the "TIME" field to bring up
records created in the last 2 hours ?

Thanks.
 
To show records created in the last 2 hours, use this criteria under the
date/time field that records the date and time the record was created:
 
Thanks Allen but unfortunately it didn't work.

On adding the fields from the database I see I have seperate columns for
"DAY" and "TIME".

In "DAY" I put Date()
In "Time" I put >= DateAdd("h", -2, Now())

I got an error "ODBC - call failed. [Microsoft][ODBC driver for
Oracle][Oracle]ORA-00932: inconsistent datatypes: expected NUMBER got DATE
(#932)

I think the time field can't be liking the Date function ?
 
Ok... ok ...

It's only after trying that suggestion it becomes clear to me; that whilst I
*DO* want to look at records from the last 2 hours, the TIME field is just
holds an integer. (Leading zeroes obviously absent.)

My mistake, I just assumed that a field labelled "Time" would have an Access
recognisable time. That'll learn me. :-)


OK I have a 6 (max) digit number in the form hhmmss

I guess to define a period of the last 2 hours I need to be able to get the
query to a) work out the integer that represents now, and then b) the integer
that represents 120 minutes ago, and look for records with values between
those 2.

Nothing's ever straightforward eh ? Would appreciate any input you have.
 
Convert the 6-digit integer into a number of seconds with an expression
such as this:
(1440 * ([T] \ 10000)) + (60 * (([T] \ 100) Mod 100)) + ([T] Mod 100)
where [T] is the field name.

You can then DateAdd() this number of seconds to your date field.

Hopefully the fields are not really named Date and Time, as both of these
are reserved words.
 
Like this you mean ?
= DateAdd("h", -2, ((1440 * (TIME \ 10000)) + (60 * ((TIME \ 100) Mod 100)) + (TIME Mod 100)))
Click to expand...

I'll put that in the criteria box and see what happens.

BTW ... yes the TITLE at the top of the time column does say "TIME", and the
one at the top of the date column does say "DATE".

Ahhhh "Data type mismatch in criteria expression" Regardless as to whether
it is put into the "DAY", or the "TIME" criteria box.

Well it was a good try. Thanks for the suggestion. I'll go think about it
some more and see what I can come up with.
 
Darn - I *knew* I'd do that.

Correction

BTW ... yes the TITLE at the top of the time column does say "TIME", and the
one at the top of the date column does say "DAY".
 
Back
Top