Determine a date based on a date range

[Jul]

Start Date and End Date with specific times

I am trying to write a query, which will pull all records from a start date that is user defined, with a specific time and an end date that is user defined with a specific time. For example, I want all records with a [Start Date] say, 10/29/2017 and a time of 6:45am, with an [End Date] say, 10/31/2017 and an end time of 6:45am. So I would want all records from 6:45am on 10/29 through 6:45am on 10/31. Here is what I tried as criteria in my query:
Between (([Start Date])+#6:45:00 AM#) And (([End Date])+#6:45:00 AM#)
But it gave me some error. Any suggestions?

 

[Gasman]

Re: Start Date and End Date with specific times

Date and time are in this format

01/11/2017 06:45:00

so try to construct a string with # at each end.

strStart = "#" & Format([yourDate],"mm/dd/yyyy") & " 06:45:00#"

HTH

 

[Jul]

Is there a way to build a field that is based on a date range? So, I want to say that everything from 6:45am yesterday to 6:45am today is considered yesterday's date. I work in a manufacturing facility and everything we report on is bases on 6:45am-6:45am being a day. So right now, we have a table where samples are taken and audited and I want to assign them to a date based on the date and time they are taken.

 

[jdraw]

My guess is yes. Since we have fiscal years and actual calendar years, it seems a CustomDay is feasible.

see this M$oft kb article for ideas

 

[arnelgp]

Put an expression:

Iif(field1 between ((Date()-1) + #6:45 am#) And (Date() +#6:45a am#), Date() -1, Date())

 

[jdraw]

Here's a function that you could try (based on arnelgp post)

Function GetCustomDay(CalendarDay As Date) As Date
'the CustomDay starts at 6:45:00 AM Yesterday and ends at 6:44.59 AM Today

    If (CalendarDay >= ((CalendarDay - 1) + #6:45:00 AM#) And CalendarDay < (CalendarDay + #6:45:00 AM#)) Then
        GetCustomDay = Format(CalendarDay - 1, "DD-MMM-YYYY")
    Else
        GetCustomDay = Format(CalendarDay, "DD-MMM-YYYY")
    End If
End Function

Here's a test routine"

Sub testCD()
'routine to test the GetCustomDay function

    Dim i As Integer
    Dim myD(3) As Date
    myD(0) = #9/20/2017 5:00:00 PM#   'expect sep 19
    myD(1) = #9/21/2017 5:00:00 AM#   'expect sep 20
    myD(2) = #9/19/2017 5:00:00 PM#   'expect sep 18
    myD(3) = #9/20/2017 6:44:58 AM#   'expect sep 19
    For i = 0 To 3
        Debug.Print myD(i) & Space(20) & GetCustomDay(myD(i))
    Next i


End Sub

And here's the result of the test routine

20-Sep-2017 5:00:00 PM                    19-Sep-2017
21-Sep-2017 5:00:00 AM                    20-Sep-2017
19-Sep-2017 5:00:00 PM                    18-Sep-2017
20-Sep-2017 6:44:58 AM                    19-Sep-2017

 

[Pat Hartman]

This is one of those rare cases where it makes sense to actually store a calculated value. I would store it because it should be indexed to make retrieval more efficient. If your reporting and searching is done on a non-indexed field, you are reduced to full table scans which get slower and slower as the record count grows.

You might be able to do this with a DataMacro if you are using ACE and will NEVER switch to a different RDBMS AND DataMacros can handle the calculation. A lot of if's.

Otherwise, in the AfterUpdate event of the control where the ActionDate is saved. Add the calculation for ReportingDate.

If TimeValue(Me.ActionDate) < #6:45 AM# then
     Me.ReportDate = DateValue(Me.ActionDate) -1
Else
    Me.ReportDate = DateValue(Me.ActionDate)
End If

To populate the ReportDate for existing records:
ReportDate:IIf(TimeValue([ActionDate]) < #6:45 AM#, DateValue([ActionDate]) -1, DateValue([ActionDate]))

 

[Pat Hartman]

Re: Start Date and End Date with specific times

Posting the same question multiple times will not get you help faster. It only wastes our time:

https://access-programmers.co.uk/forums/showthread.php?t=296725

 

[pbaldy]

Duplicate threads merged since both had replies.

 

[Mark_]

Relevant note regarding using dates; if you set the date to be INT(Date), you can strip the time portion. Useful for some applications where you want a match without having to use a range.

 

[Jul]

Re: Start Date and End Date with specific times

Posting the same question multiple times will not get you help faster. It only wastes our time:

https://access-programmers.co.uk/forums/showthread.php?t=296725

I'm sorry if it seemed like I was asking the same question. I was actually trying to ask 2 different questions. One was to use criteria to get just that specific information and the other was to go back in history and figure out the correct date and use that going forward as well. I didn't mean to waste anybody's time or try to ask the same question twice. My apologies.

 

[arnelgp]

use jdraw's method.

 

[Pat Hartman]

You obviously didn't test it.