Date Filter - not pulling end date

[MHamid]

Hello,

I have a form with a date range filter.

The code that I am using for the date range is:

 strDateRange = "([DateTime] >= #" & Me.txtStartDate & "# And [DateTime] <= #" & Me.txtEndDate & "#)"

When I use the filter start date of 12/4/2017 and an end date of 12/15/2017. The filter will only show dates between 12/4/2017 and 12/14/2017. From what I understand, Access has a default value date of midnight. What would I need to do to ensure the date range I am selecting (12/4/2017 to 12/15/2017) is actually picking up everything that I have selected and not dropping the last date because of the default date/time value of midnight.

And yes, my field is a date/time field.


Thank you

 

[Joe4]

Add one day to your txtEndDate variable, and use "less than" instead of the "less than or equal to".
So then you have < "12/16/2017". So, it will pick up everything from the 15th, regardless of time, and nothing from the 16th.

 

[Micron]

For sure that's the simplest solution - as long as users understand how to input dates. If they don't know to add a day to the date they actually want, they'd have to be trained. Or you could say nothing and add one day in the query. Either way invites possible problems unless maybe you have an instructional label on the form, and we all know that all users follow all instructions when prompted, right?

Alternatively, you can add hours, minutes or seconds to the end date using DateAdd function.

 

[xenou]

As a variation on the theme, you might convert the dates dates into without times. The +1 strategy can then be used, with the result being "all records on these dates, regardless of the time values", and would work for single day queries as well as multiple day queries.

dim beginDate as Date
dim endDate as Date
dim strDateRange as String

//Filter for all records between begin and end dates, inclusively, regardless of time values

beginDate = CDate(Me.txtStartDate)
beginDate = DateSerial(Year(beginDate),Month(beginDate),Day(beginDate))

endDate = CDate(Me.txtEndDate)
endDate = DateSerial(Year(endDate),Month(endDate),Day(endDate))

strDateRange = "([DateTime] >= #" & beginDate & "# And [DateTime] [B][COLOR="#FF0000"]<[/COLOR][/B] #" & (endDate [B][COLOR="#FF0000"]+ 1[/COLOR][/B]) & "#)"

 

[MHamid]

Hello,

All of your suggestions work perfectly.
I do get an error message after I enter the start date. So I had to code in a message box within an if statement letting the user know that an end date is needed instead of getting the standard error code message.

If IsNull (Me.txtStartDate) or IsNull (Me.txtEndDate) Then
MsgBox "Please enter the date range", vbInformation, "Date Range Required"
Me.txtStartDate.SetFocus
Else
strDate \Range= (REST OF THE CODE GOES HERE)

Was this the best way to handle that situation?

Thank you

 

[Micron]

If IsNull (Me.txtStartDate) or IsNull (Me.txtEndDate) Then
MsgBox "Please enter the date range", vbInformation, "Date Range Required"
Me.txtStartDate.SetFocus
EXIT SUB 
Else
strDate \Range= (REST OF THE CODE GOES HERE)

[/QUOTE]
Code execution will continue if you don't exit.
Do you really have a / in your variable name?

 

[MHamid]

Hello,

No, there is no \ in my variable. that was a typo in my part here.
Also, I got an error message stating that exit sub is not allowed in function or property.

I probably should have mentioned that this code is included in part of my function code.

Thank you

 

[Joe4]

Did you try to incorporate anything I suggested?

 

[MHamid]

Hello,

This is my full code:

[Function Search()
Dim strUser, strAction, strDateRange As String
Dim beginDate, endDate As Date
Dim task, strCriteria As String
If IsNull(Me.cboUser) Then
    strUser = "[UserName] like '*'"
Else
    strUser = "[UserName] = '" & Me.cboUser & "'"
End If
If IsNull(Me.cboAction) Then
    strAction = "[Action] like '*'"
Else
    strAction = "[Action] = '" & Me.cboAction & "'"
End If
Me.Refresh
If IsNull(Me.txtStartDate) Or IsNull(Me.txtEndDate) Then
    MsgBox "Please enter the date range", vbInformation, "Date Range Required"
    Me.txtStartDate.SetFocus
Exit Sub
Else
    strDateRange = "([DateTime] >= #" & Me.txtStartDate & "# And [DateTime] < #" & (Me.txtEndDate + 1) & "#)

strCriteria = strUser & "And" & strAction
    task = "select * from tbl_AuditTrail where (" & strCriteria & "And" & strDateRange & ") order by AuditTrailID"
    DoCmd.ApplyFilter task
End If
End Function
/CODE]

It works with the exception of "Exit Sub".
I don't think I need it and I don't see why it would be needed. 

Thank you

 

[Fluff]

Replace it with

Exit Function