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]

the message box keeps popping up when I select a User, an Action, and a start date.

So you want to run this if there are 2 dates provided, but still run it if no dates are provided? Then you have to decide, but I think it's a case of
a) if both dates are provided, use them - or
b) if one is missing, they both have to be provided or the single date has to be cleared - or
c) if no dates are provided, create the filter without dates

If that's it, the answer (IMHO) would be to use IF blocks to decide on how to build the expression. Anything that must be used (required criteria, perhaps a person's name plus key words like WHERE & such) and concatenate either the date part onto it, or not. Then apply the filter as normal. If that's not what you're after, then I am not following I guess. Maybe you're getting the prompt when you try to select from combo boxes - you don't say when. In that case, there's something amiss about the row sources behind your combo(s) or whatever.

 

[xenou]

If the user doesn't supply dates you can also use defaults (i.e., a very low date and a very high date -- 1/1/1900 and 12/31/2999, which would in most situations cover all possible dates in your dataset)

 

[MHamid]

Hello,

xenou, I used your suggestion and I like how it works.

My next issue now is that when I clear the search form options, I would then get a type mismatch error message when I try to do another search.

The code I am using for the clear filter button is:

Private Sub ClearFilter_Click()
DoCmd.RunCommand acCmdRemoveAllFilters
Me.cboUser = ""
Me.cboAction = ""
Me.txtStartDate = ""
Me.txtEndDate = ""
End Sub

The form will clear the combo boxes and the date text boxes. However, when I try to select another user, I am getting a type mismatch error message and the filter function part of the code highlighted is:

beginDate = CDate(Me.txtStartDate)

What did I miss now?

Thank you

 

[xenou]

Hi, this is one pretty straightforward. "" is not a valid value for a date, or more specifically you cannot cast a text value of "" to a date.

 

[MHamid]

Hello,

That makes sense. I don't like that I would have to leave it as 01/01/1900 and 12/31/2999, but as long as I don't get the error message that is fine with me.
Thank you for all your help.