VBA Filter for form Date range

Question

I have VBA Code that I use to filter a form in my DB.  The code works but I wanted to add a date range function to it and this is where I'm having issues.

The Bold section is where I think the problem(s) lie. 

Sub ReportFilter()

 On Error Resume Next

   Dim strWhere As String                 'The criteria string.

   Dim lngLen As Long                     'Length of the criteria string to append to.

   Dim strDateField As String

   Dim lngView As Long

   Const conJetDate = "#mm/dd/yyyy#"   'The format expected for dates in a JET query string.

   strDateField = "[TODATE]"

   If IsNull(Me.LocationSel) Then

       strWhere = strWhere & "([Location] Like ""*" & Me. Location.Column(0) & "*"") AND "

   Else: strWhere = strWhere & "([Location] Like ""*" & Me. Location.Column(0) & "*"") AND "

   End If

   If Not IsNull(Me.IteamSel) Then

       strWhere = strWhere & "([Iteam] Like ""*" & Me.IteamSel.Column(0) & "*"") AND "

   End If

If IsDate(Me.StartDSel) Then

strWhere = strWhere & "([TODATE] >= " & Format(Me.StartDSel, strcJetDate) & ") AND"

End If

---

If IsDate(Me.EndDSel) Then

If strWhere <> vbNullString Then

strWhere = strWhere & " AND "

End If

strWhere = strWhere & "([TODATE] < " & Format(Me.EndDSel + 1, strcJetDate) & ")"

End If

   lngLen = Len(strWhere) - 5

   If lngLen <= 0 Then     'Nah: there was nothing in the string.

       MsgBox "No criteria", vbInformation, "Nothing to do."

   Else                   'Yep: there is something there, so remove the " AND " at the end.

       strWhere = Left$(strWhere, lngLen)

       'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).

       'Debug.Print strWhere

       'Finally, apply the string as the form's Filter.

       Me.Filter = strWhere

       Me.FilterOn = True

   End If

End Sub

Answer 1

That is strange as I cannot see why that error would be triggered.  ConJetDate is declared as a constant

You might try changing the lines to the following and see if it still errors

strWhere = strWhere & " AND [TODATE] >= " & Format(Me.StartDSel,"#yyyy-mm-dd#")

strWhere = strWhere & " AND [TODATE] < " & Format(DateAdd("d",1,Me.EndDSel,"#yyyy-mm-dd#")

Also try

Const conJetDate as String = "#yyyy-mm-dd#"

---

John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County

Answer 2

Sub ReportFilter()

 'On Error Resume Next  'Remove this until things are working.

'It is usually (not always) a bad idea to have this in your code. 

'Much better is to add some error trapping code and handle any errors

   Dim strWhere As String                 'The criteria string.

   Dim lngLen As Long                     'Length of the criteria string to append to.

'   Dim strDateField As String  'Unused variable

'   Dim lngView As Long         'Unused variable

   Const conJetDate = "#mm/dd/yyyy#"   'The format expected for dates in a JET query string.

   strDateField = "[TODATE]"

 'I prefer to add " AND " to the beginning and eliminate all unneeded parentheses

   If Not IsNull(Me.LocationSel) Then

       strWhere = strWhere & " AND [Location] Like ""*" & Me. Location.Column(0) & "*"" "

   End If

   If Not IsNull(Me.IteamSel) Then

       strWhere = strWhere & " AND [Iteam] Like ""*" & Me.IteamSel.Column(0) & "*"" "

   End If

   If IsDate(Me.StartDSel) Then

      strWhere = strWhere & " AND [TODATE] >= " & Format(Me.StartDSel, conJetDate)

   End If

   If IsDate(Me.EndDSel) Then

      strWhere = strWhere & " AND [TODATE] < " & Format(Me.EndDSel + 1, conJetDate)

   End If

   lngLen = Len(strWhere)

   If lngLen < 5 Then     'Nah: there was nothing in the string.

       MsgBox "No criteria", vbInformation, "Nothing to do."

   Else 'there is something there, so remove the " AND " at the start of the string

       strWhere = Mid(strWhere, 6)

       'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).

       'Debug.Print strWhere

       'Finally, apply the string as the form's Filter.

       Me.Filter = strWhere

       Me.FilterOn = True

   End If

End Sub

---

John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County

Answer 3

Do you get an error message? If so, what does it say?

Do you get unexpected results? If so, in what sense?

Answer 4

Ok, got that changed to conJetDate.  But it's still not working.

Answer 5

You use strcJetDate in the strWhere lines but the actual name of the constant defined higher up in the code is conJetDate.