Query filering by time is not working

[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.

 

[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

 

[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

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

 

[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

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

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