Link to home
Create AccountLog in
Avatar of TSTechNA

asked on

Access Form Display times from 5am to 4:59am next day

I have an an Access 2010 form called frmIssue.
It displays a list of records from the tblIssue table.

I am trying to create a button, that when clicked, displays records from yesterday at 5am to today at 4:59 am (24 hours overlapping dates). I want it to display in the tblIssue format.

My date and time is being recorded in two separate fields (IssueDate and IssueTime).

I'm not sure how to do this at all.
I don't know whether to call a query, create a macros, etc.

Also, I'd prefer not to have to combine the date and time fields into one field in tblIssue.
Avatar of IrogSinta
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of TSTechNA


I tried this code in the OnClick event of the button and while I get no errors, it also returns no data. I have a test record in there with date of 11/11/12 and 11:00 PM. I am using AM/PM and not military time, if that makes a difference.
I just tried creating this out and it works okay, with the exception that I incorrectly gave you #4:49# instead of #4:59#

Are both your fields, set to Date data types or are they Text?
They are both datetime data types.
Are you able to upload a stripped down version of your database (less any privacy info)?
Is the list of records inside a subform or listbox on the main form?
The database consists of linked SQL tables on the backend. Do you want me to import tblIssue and send it?
Do a test first with an Access table to see if it works vice a linked SQL table.
So the list is not inside a subform or list box, right?
No. It is a main form.
Ok. It works with an imported table. I stripped all tables except for tblIssue and created 3 fake records. Also, all forms except for frmIssue have been deleted, so the Start menu is missing. You'll have to hold shift when opening.
You may have problems with SQL date/time values if you have no latest SQL client. Try to use forced conversion to date/time. Something like this:
Me.Filter = "(Datevalue([IssueDate])+TimeValue([IssueTime])) Between (Date()-1)+#5:00# And (Date()+#4:49#)"
Yeah, what he said.
Sorry forgot to attach.
Actually, I apologize, the code doesn't work with the imported table, but the form does. Sorry I wasn't clear on what "it" was.
Applying the filter seems to work fine.  Clearing the filter gave errors so I revised that portion to:
Private Sub ClearFilters_Click()

    Me.FilterOn = False

    If Me.Dirty Then
       DoCmd.RunCommand acCmdSaveRecord
    End If

End Sub

Open in new window

Thanks for the correction on the Filter code. It worked great.

As for the OnClick Event for my ABShift button, I am using the following code, letter for letter:

Private Sub ABShift_Click()

Me.Filter = "([IssueDate]+[IssueTime]) Between (Date()-1)+#5:00# And (Date()+#4:59#)"
Me.FilterOn = True

End Sub

Open in new window

All I get is a blank record with (New) where the record number should be on the form.
Since this works fine with Access as the table, the problem probably is with using a linked SQL table.  Add the following to line3 and post what you get in the immediate windows:
Debug.Print [IssueDate], [IssueTime], [IssueDate]+[IssueTime]
11/13/2012    9:54:00 PM    11/13/2012 9:54:00 PM
Null          Null          Null
11/13/2012    9:54:00 PM    11/13/2012 9:54:00 PM
So it looks like you're not supposed to get any records since the date and times are after 5AM this morning.
I see what it's doing. On the debug.print, it's only displaying the top record on my form.

I just sorted it on the form from oldest to newest, and it displayed this in the Immediate window when I clicked my Apply Filter button:
4/4/2012      12:00:00 PM   4/4/2012 12:00:00 PM
Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Ok. That fixed it. I also had some Nulls on the time field, which the above code complained about. It is working beautifully. Thanks for all of your help and sorry about complications.