Simple Query Criteria

[ccordner]

Hi

I have a query including the fields "DateReported" and "TimeReported"

I want to show only events reported between 4am yesterday and 3:59am today.



How do I do it?

Thanks
Chris

[orange]

How are these fields defined in the underlying tables?

If in 1 field with date and time e.g. 12/3/2012 4:45PM
then, to get the Between A and B

Sub FourAMTo359()

    Dim myDate As Date
    myDate = #12/17/2012#
    myDate = DateAdd("n", 240, myDate)    'get the 4:00AM into the myDate field
    Debug.Print "between " & Format(myDate, "mm/dd/yyyy  HH:NN") _
                & " and " & DateAdd("n", 1439, myDate)   ' 1439  is 1 min less than 24 hours --> 3:59AM
End Sub

if you use 2 fields, need info to see the table and date and time fields.

[orange]

I don't know what your fields are called, but here is a sample procedure that uses separate date field and time field.
This procedure adds the time values to the date values, then compares them to be with your date range
(>=rangelow and <=rangeTop)

yesterday 4:00 AM up to Today at 3:59 AM. I used Dec 2 and Dec 3 for the test.

Sub Datetimeflds()
'if there are separate date and time fields
'here is a sample to show adding date and time values, and
' to compare some test dates to an acceptable date range( rangeLow -- rangeTop).
    
    Dim rangeLow As Date
    Dim rangetop As Date
   
   On Error GoTo Datetimeflds_Error

    rangeLow = #12/2/2012#
    rangeLow = DateAdd("n", 240, rangeLow)
    Dim fulldate As Date
    '
    'set up an array of dates to test
    Dim dtarray(4, 1) As Date
    dtarray(1, 0) = #12/2/2012#
    dtarray(1, 1) = #3:45:00 PM#
    dtarray(2, 0) = #12/1/2012#
    dtarray(2, 1) = #7:05:00 PM#
    dtarray(3, 0) = #12/2/2012#
    dtarray(3, 1) = #11:17:00 PM#
    dtarray(4, 0) = #12/3/2012#
    dtarray(4, 1) = #4:01:00 PM#

   ' fulldate = DateAdd("n", (Hour(mtime) * 60) + Minute(mtime), rangeLow) 'for debugging
    
    rangetop = DateAdd("n", 1439, rangeLow)
    Debug.Print "Date Range to check    " & rangeLow & "  " & DateAdd("n", 1439, rangeLow)
    For i = 1 To 4
        testdate = DateAdd("n", Hour(dtarray(i, 1)) * 60 + Minute(dtarray(i, 1)), dtarray(i, 0))
        If testdate >= rangeLow And testdate <= rangetop Then
            Debug.Print testdate & "   is in range"
        End If
    Next i

   On Error GoTo 0
   Exit Sub

Datetimeflds_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Datetimeflds of Module Jed"
End Sub

Hope it's helpful to you.

[ccordner]

Yes, sorted it thanks.

Chris