How to filter for...

[sking89]

I have a database that produces a report for everything that has been entered since the day before at 5:00:00 AM. I have tried everything but it seems like I can only do either or for the filter... either the yesterday Date ()-1 or the time >=#5:00:00 AM#. I cant seem to combine them...



Can anyone help or does anyone have any ideas?

Thanks,
very frustrated access user

[June7]

You have date and time in separate Date/Time datatype fields in table? Query would look like:
SELECT *, Table1.DateEnt, Table1.TimeEnt
FROM Table1
WHERE (((Table1.DateEnt)>=Date()-1) AND ((Table1.TimeEnt)>=#12/30/1899 5:0:0#));

[sking89]

no, I have one field with general date format so date and time are together... but it wouldnt be hard to seperate them...

[June7]

You don't have to restructure the table but do need to extract the date/time parts for purpose of this query. Example:

SELECT *, CDate(Format([DateAll],"mm/dd/yyyy")) AS D, CDate(Format([DateAll],"hh:nn:ss")) AS T
FROM Table1
WHERE (((CDate(Format([DateAll],"mm/dd/yyyy")))>=Date()-1) AND ((CDate(Format([DateAll],"hh:nn:ss")))>=#12/30/1899 5:0:0#));

[sking89]

YOU ARE MY HERO! Thank you soooo much!

[June7]

Actually, I did't get it quite right. That query will exclude records with a time earlier than 5 AM for every day. No need to extract the date/time parts after all. I think this is what you were looking for:
SELECT * FROM Table1
WHERE Table1.DateAll>=Format(Date()-1,"mm/dd/yyyy") & " 5:00:00 AM");

[sking89]

Sorry for the belated response. The second post worked great. Thank you so much for your help!