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

 

[Micron]

That's what happens when we have to guess. Doesn't help that I had to use my phone because my laptop died.

 

[MHamid]

yes, my mistake for not providing the full code.

I have another issue with this code. It was working perfectly when filtering before I added the date range.
When I start to filter by the user, I am getting a data type mismatch error message. The user is supposed to have the option to filter by all three options or by just one or two criteria.

The error is highlighting this part of the code:

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

Where did I go wrong in my code?

Function Search()
Dim strUser, strAction, strDateRange As String
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 Function
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

Thank you

 

[Joe4]

What are your values of "txtStartDate" and "txtEndDate" when it is run?
Note that if they REALLY are Text values, and not Date values, you cannot add 1 to them without first converting it to a date.

 

[xenou]

Make sure you are not ending up with a string like this:

([UserName] like 'Mary'AND[DateTime] >= #1/1/2017# AND [DateTime] < #1/2/2017#)

should be:

([UserName] like 'Mary' AND [DateTime] >= #1/1/2017# AND [DateTime] < #1/2/2017#)

i.e., watch them spaces and don't mush things together when concatenating these various criteria.

You won't need an Exit Function if your intention is to have the dates not be used if the values are null. You would need the Exit Function if you want to completely stop if there are no dates.

See last post about a possible problem in the erroring line.

 

[MHamid]

joe4,

These are both date values, not text values. The user has the option to enter the dates or select them from a calendar. I just labeled them like this ... sorry about the confusion.

 

[xenou]

If the control is unbound (or bound to field that is not a date field), then probably it is text. The entry method wouldn't matter - whether typed in or picked from a calendar. So very likely you have dates stored as text. See my original post - as you can see, the CDate() function is needed to convert text to true dates (warning: an exception would result if an invalid value was typed in such as "first of december 2017")

Also, by the way using a CDate() function on a date wouldn't hurt either - so you might as well try it.

 

[Micron]

I can't test this right now - still recovering from my pc loss.
Take a look at the error line if none of the prior replies help. What is (Me.txtEndDate + 1)?
You don't have a control called txtEndDate+1 right? At least that's my guess that Access is interpreting it that way since you have not wrapped the field/control name in brackets, but have put parentheses around the whole thing.
One fix might be (Me.[txtEndDate] + 1), or (Me.txtEndDate) + 1 as long as you use the correct number and arrangement of ending )'s.
Another would be to perform calcualtions prior to constructing an expression, as in
xxxEndDate = Me.txtEndDate + 1 (or Me.[txtEndDate] + 1)

"...And [DateTime] < #" & xxxEndDate & "#)"

where you have dim'd xxxEndDate as a variable. I put xxx because in your case, I'm not sure if a date, text or long integer type is best. Just popping in for a sec so I haven't reread everything.

 

[MHamid]

Hello,

Ok, I see what you're saying and I made the changes.

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
Else
    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] < #" & (endDate + 1) & "#)"
strCriteria = strUser & "And" & strAction
    task = "select * from tbl_AuditTrail where (" & strCriteria & " And " & strDateRange & ") order by AuditTrailID"
    DoCmd.ApplyFilter task
End If
End Function

This code works. However, I don't want a message box to popup requiring a date range. I don't need a date range to be required. So, I am not sure what to replace that piece of the code if the date range is null. Any suggestions?

Micron, sorry about your pc loss.

Thank you

 

[Micron]

Thanks. So what I suggested fixed the prior issue? Normally I would pop open Access and test something when I'm not 100% sure. Sometimes the issue is that I wouldn't code the way someone else has, so unless you've had the experience of the syntax problem, you can't be sure.
As for the prompt, no part of a date range can be null AFAIK. If you don't want to be prompted, the info has to be grabbed somehow. You have

MsgBox "Please enter the date range", vbInformation, "Date Range Required"

yet you don't want a prompt? I'm confused by your statements.
I will look more closely at your code while I await your clarification - there are some potential issues.

 

[MHamid]

Hello,

I did not try your suggestion, Micron.
I used Xenou's original suggestion.


I know my code has the message box stating that the date range is required. However, the message box keeps popping up when I select a User, an Action, and a start date.
So I was rethinking this date range requirement. I want to actually not make it a requirement and allow the fields to be null. What would I need to change that line of coding?


Thank you