How to Capture Changed Records After Specified Time

[Tblqryfrmrpt]

Sorry for the odd title, but my problem is a little tricky.

I have a query that is a union query that captures all incoming and outgoing changes to a field in a record based on today's date. These records need to be emailed to an outside source. The idea is that an admin user can click the button when the report needs to be sent, write an Excel file and then send an Outlook email. This part works fine.

My problem is that this report is typically run at around 2 pm. For records changed after 2 pm, if someone goes to pull the report tomorrow, the changes will not show up because the query is based on today's date. Even if it was based on today and yesterday's date, it will also return the records that were changed before 2pm the day prior. Since I don't currently track the time, just the date, this leads me to a big problem.

My thought is that instead of a query, I will have to create a new table with a date AND time stamp and then the button will pull everything after x time and y date. However, this is much more complicated than the simple query I have now. Does anyone have any another ideas how to work around this issue?

Thanks!

 

[Beetle]

You certainly don't need to create a new table. As long as the field in your existing table where the date is stored is a Date/Time data type, it already stores the time as well. It's just that if a time value is not specifically entered when the record is created it assumes a time of 12:00:00 am for each record. All you need to do is modify your data entry process slightly so that instead of the users entering a date you do it programatically. You would typically do this in the Before Update event of your form using the Now() function, which returns the current date and time. Code might look like;

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
If Me.NewRecord Then
    Me!YourDateField = Now()
End If
 
End Sub

Then in your query you would use criteria like the following for the DateField;

Between Date()-1 & " " & #2:00:00 PM# And Date() & " " & #2:00:00 PM#

The only caveat here is that you have to make sure the report is run after 2:00 pm each day, or you could end up missing records that were entered between, say 1:58 pm and 2:00 pm.

 

[PNGBill]

What about adding a small Field to the table to act as a flag.
Default is N and as the qry performs it task, the field is changed to Y.
The qry will not collect data where the flag is Y.