Query filering by time is not working

A

Adam Thwaites

I am trying to filter my data so that a record that has been accesses in the
last 5 hours does not come up. I have a SaveDate field (dd/mm/yyyy) and a
SaveTime (hh:mm:ss) field.
My criteria for the SaveDate field is <(Now()-0.208) Or Is Null but as the
SaveDate is only storing the date it is not working properly. I'v tried to
combine the fields for the query:
SavedWhen: SaveDate & " " & SaveTime but I cannot get this into the same
format as (Now()-0.208). This is what I need help with. I'v tried SavedWhen:
CDate([SaveDate] & " " & [SaveTime]) and several other variations but I just
keep getting datatype mismatch errors.
 
J

Jeff Boyce

Adam

Access has a date/time data type. There's no need to have both a SaveDate
and a SaveTime field. You can use a query to get the DateValue() and
TimeValue() portions.

If you want to use Now() - .208, use the combined date/time field.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
 
A

Adam Thwaites

That seems like an easy way out. I prefer having my dates and times saved in
a seperate field. Surely there is a solution to this problem without having
to combine fields.
--
Adam Thwaites
Access Database Designer
adam.*spamless*[email protected]
Manchester, UK


Jeff Boyce said:
Adam

Access has a date/time data type. There's no need to have both a SaveDate
and a SaveTime field. You can use a query to get the DateValue() and
TimeValue() portions.

If you want to use Now() - .208, use the combined date/time field.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

Adam Thwaites said:
I am trying to filter my data so that a record that has been accesses in the
last 5 hours does not come up. I have a SaveDate field (dd/mm/yyyy) and a
SaveTime (hh:mm:ss) field.
My criteria for the SaveDate field is <(Now()-0.208) Or Is Null but as the
SaveDate is only storing the date it is not working properly. I'v tried to
combine the fields for the query:
SavedWhen: SaveDate & " " & SaveTime but I cannot get this into the same
format as (Now()-0.208). This is what I need help with. I'v tried SavedWhen:
CDate([SaveDate] & " " & [SaveTime]) and several other variations but I just
keep getting datatype mismatch errors.


--
Adam Thwaites
Access Database Designer
adam.*spamless*[email protected]
Manchester, UK
 
J

Jeff Boyce

Adam

It isn't a matter of having to combine. Access date/time data type is
design to hold ... date and time. If you want to be able to "see" the
date-only or time-only portion, that's what queries are for.

If you want to compare Now() to something, you have to compare to a combined
date and time -- pay now or pay later!

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

Adam Thwaites said:
That seems like an easy way out. I prefer having my dates and times saved in
a seperate field. Surely there is a solution to this problem without having
to combine fields.
--
Adam Thwaites
Access Database Designer
adam.*spamless*[email protected]
Manchester, UK


Jeff Boyce said:
Adam

Access has a date/time data type. There's no need to have both a SaveDate
and a SaveTime field. You can use a query to get the DateValue() and
TimeValue() portions.

If you want to use Now() - .208, use the combined date/time field.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

I am trying to filter my data so that a record that has been accesses
in
the
last 5 hours does not come up. I have a SaveDate field (dd/mm/yyyy) and a
SaveTime (hh:mm:ss) field.
My criteria for the SaveDate field is <(Now()-0.208) Or Is Null but as the
SaveDate is only storing the date it is not working properly. I'v tried to
combine the fields for the query:
SavedWhen: SaveDate & " " & SaveTime but I cannot get this into the same
format as (Now()-0.208). This is what I need help with. I'v tried SavedWhen:
CDate([SaveDate] & " " & [SaveTime]) and several other variations but
I
just
keep getting datatype mismatch errors.


--
Adam Thwaites
Access Database Designer
adam.*spamless*[email protected]
Manchester, UK
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top