Time Problem

[Vassago]

I've got a serious problem I need fixed today.

I have a table with three fields

type
date
time

I need to devise a query that will give me anything from this table from yesterday's date after 2:00 pm until today at 2:00 pm. I need this to be doable every day. It should be a 24 hour period, just overlapping into the next day. I just can't come up with the formula to make this happen overlapping into the next day. Any ideas?

Thanks,

Vassago
"Those that make the best use of their time have none to spare" Thomas Fuller.

 

[sambo]

Why not just use one field and populate it with the Now() function? This gives you date and time in one field. This would also allow you to control your selections w/o concatenating the 2 fields.

 

[Vassago]

Thanks for the quick reply, however...

I didn't create the database, it's already set up that way, but I did make a query that put the two fields together. I also tried this criteria on the field but it did not work.

Between ([Start_Date] & " " & Format("2:00:00 PM","hh:nn:ss")) And ([End_Date] & " " & Format("1:59:59 PM","hh:nn:ss"))

Maybe I'm typing it wrong?

Any more help would be appreciated,

Thanks,

Vassago

 

[sambo]

Here is all I can come up with.

In Excel, if you enter a date/time..
6/1/1999 2:00:00 PM

And then change the Format of that cell to General Number.

The result of that cell then becomes..
36312.58333

The numbers to the left of the "." represent the day, and the numbers to the right represent the time.

So..
.5833 equals 2 PM, now all you have to do is define your day to be Date().
Lets say Date() equals 36312 (or 6\1\1999)
Now maybe you could take all numbers between 36311.58333 (yesterday at 2 o'clock) and 36312.58333 (today at 2 o'clock).

The only problem will be exporting to excel and then re-importing the newly creating "General" style Date.

This is just a thought. It could probably all be done in code.

Good luck in finding a better solution, now that I think about it this one pretty much sucks.