Time difference in Query

[Dave Eyley]

Hi All,

I want to build a query to select records based on time. My table contains the records -

LogDate
LogTime (Short Time format)

I want the query to pull all the records for 24hrs back from now...

I've tried Between LogDate AND LogDate-1 AND Between LogTime And LogTime-24

But it doesn't work

I've tried using the DateAdd() function to no avail.

Any ideas?

 

[namliam]

Why did you split the date and time?? Access is date/time combined...

Anyhow...
Logdate + logTime > Now() - 1
Will resolve your issue.

 

[Dave Eyley]

Thanks namliam,

I wouldn't have got there in a month of Sundays...
The reason I have a separate time and date is that it's a security event records system ands needs to show the day, date and time in one row of a multirow subform...It seemed the most convenient way.
It also makes reports from the table easier to create.

Thanks again

Dave

 

[namliam]

You can display time and date split just by showing the same date/time field twice with different formats, without the need to actually split them in your db.

You basicaly should never split date/time in anycase... in any db...
(with some exceptions to confirm the rule offcourse)

 

[Dave Eyley]

I didn't split them - they were never together

They are separate fields in their own right. One derived from Date() and one from Now()...

It was just the way I designed it...it works well and I can't any problem with it...it's been working nicely for 4 years without a hitch...

Can you explain why you should never separate date/time fields?

Dave

 

[namliam]

Aha! But NOW() contains the date as well...
So probably your adding together should fail, because you are adding Date + now(), which is actually DAte + Date + Time.
If your LogTime is actually Now() then: logTime > Now() - 1 should do....

Sure it will work fine, no problems. The problem with splitting date/time is that one is useless without the other.
In particular time is dependant upon date, like your question, I want the last 24 hours is hard/impossible to do if you dont have the date component.

Furthere more (less of a priority now a days) it consumes (useless) space.