DateAdd issue

[edmscan]

I am trying to retrieve records for the past 24 hours .. from a table. I have 2 DateTime fields .. named TransDate and TransTime containing for example 2014/09/25 and 12:45:45 PM respectively.
(I know they are not stored this way ... )

However .. because they are in separate fields .. I cannot find how to make this happen. If they are in one field in the table .. ie 2014/09/25 12:45:45 PM then I can use DateAdd as below and it works fine.
I can use criteria of .. DateAdd("s",-86400,Now()) .. and I get the 2 records I expect.

I do not want to combine them into one field in the table .. as that just complicates things elsewhere, and there are over 4 million records in the table .. and I want to keep the table as small as possible

I am using this in a query .. and need a criteria that will work. The below does not work ...

Select * from Transmissions Where TransDate > DateAdd("s",-86400,Now()) And TransTime > DateAdd("s", -86400,Now());

[June7]

Maybe:

WHERE TransDate + TransTime > DateAdd("s", -86400, Now())

[edmscan]

Ok .. tried it. I get no results.

Also .. tried putting CDate in there .. and same thing.

[edmscan]

Well .. I found my solution. I combined the 2 fields .. and interestingly it did not increase the size of the file at all. So .. I have my solution. The query will work just fine when the fields are combined .. but if
you have a thought on how to do it with 2 fields I would be interested in hearing about it.

Thanks June7.

[ssanfu]

You could combine the fields in the query....

Query1 is the data set - no criteria
Query2 is the data set - with criteria

[edmscan]

Thanks ssanfu .. that works perfectly.

[ssanfu]

you have a thought on how to do it with 2 fields I would be interested in hearing about it.

Do you mean like Query3?

I think 84600 seconds = 1440 minutes = 1 day, so you can use > DateAdd("s", -86400, Now()) for the criteria for TransDate.
Then have to limit TransTime. You are using Now(), so get the current time as the criteria for TransTime.

Maybe??

[edmscan]

Either query 2 or 3 seem to work just fine with 2 fields. And .. my results are returned pretty fast. I am going to work on optimizing the 4 queries I did today.