Pull Records Since Last Check

[athyeh]

Hi Folks,



I am looking for a query design that will only return records since the last check.

I have tried to accomplish this by operating off the Time Stamp each record gets "mm/dd/yyyy hh:nn:ss AM/PM"

On the Form I have the user key a time: 6:00 PM

In the Query Criteria for the Time Stamp Field I specify: >[Forms]![Form1]![Since]

But the query continues to return all records.

Please help troubleshoot!

Thanks~!

[ItsMe]

The field 'Since' is of date/time data type? When you look at the table where the value is stored, what does the text look like? Can you paste an example of a populated field here?

[June7]

That's because when date is not specified, Access assumes 12/30/1899.

[athyeh]

Thank you for your replies, the field is set to "General Date" format.

1/9/2014 6:29:14 PM is the Time Stamp given for the "Time_Req" field.

Surely there is a way to create the form where the user does not have to write the desired 'time' criteria in that exact format?

[ItsMe]

So "Since" is an alias? Perhaps you should include Time_Req in your criteria as well

[June7]

Fortunately, I've never had to do searches/filters with time criteria in my dbs. I find it problematic.

Do you want to assume the current date in the criteria?

Do you want users to enter time criteria on the hour, half-hour, quarter-hour, any time?

Let's say on the hour. Use a combobox that lists the hours (01:00 AM, 02:00 AM, etc). Noon and midnight are tricky: 12:00 PM and 00:00. An alternative is 24-hr clock time.

CDate(Date() & " " & Me.combobox)

The format setting has no bearing on how the date value is stored. The concern is whether the field is a text or date/time type.