Filter sub form between two dates in text fields

[james_IT]

My subform filters fine with combo boxes. One of the fields in the subform is a date which I would like to be able to filter from a txtStartDate and txtEndDate.

I'm using the following code but I cant get it working (getting an error code 3075)

Public Sub SetFilter()
On Error GoTo Err_strFilter_Error
Dim strFilter As String
Dim ctrl As Control

  If Not IsNull(Me.OptionName) Then
    strFilter = strFilter & "((OptionName = '" & Me.OptionName & "')) AND "
  End If
  
  
    If Not IsNull(Me.Development) Then
    strFilter = strFilter & "((Development = '" & Me.Development & "')) AND "
  End If


   If Not IsNull(Me.ArtworkSource) Then
    strFilter = strFilter & "((ArtworkSource = '" & Me.ArtworkSource & "')) AND "
  End If
  
    If Not IsNull(Me.Publication) Then
  strFilter = strFilter & "((Publication = '" & Me.Publication & "')) AND "
End If
     
     
         If Not IsNull(Me.PageSize) Then
  strFilter = strFilter & "((PageSize = '" & Me.PageSize & "')) AND "
End If
     
[COLOR="Red"][B]     If Not IsNull(Me.txtStartDate) Then
strFilter = "[BookingDate] BETWEEN #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "#"

     End If[/B][/COLOR]
     
     
     
  If Len(strFilter) > 0 Then
    strFilter = Left(strFilter, Len(strFilter) - 5)
    Me.FilteringQuerySubform.Form.Filter = strFilter
    Me.FilteringQuerySubform.Form.FilterOn = True
    Me.SubLabel.Caption = "FILTERED"
  Else
    Me.FilteringQuerySubform.Form.FilterOn = False
    Me.SubLabel.Caption = ""
    For Each ctrl In Me.Controls
      If ctrl.ControlType = acComboBox Then
        ctrl.Value = Null
      End If
    Next
  End If
  
Exit_ErrorHandler:
  Exit Sub

Err_strFilter_Error:

  MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SetFilter of VBA Document Form_FilteringQuerySubForm at Line " & Erl
  Resume Exit_ErrorHandler

End Sub

I had the dates filter activating on command button:

Private Sub cmdDateSearch_Click()
On Error GoTo Err_cmdDateSearch_AfterUpdate_Error

  SetFilter

Exit_ErrorHandler:
  Exit Sub

Err_cmdDateSearch_AfterUpdate_Error:

   MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdDateSearch_AfterUpdate of VBA Document Form_SearchF at Line " & Erl
  Resume Exit_ErrorHandler
End Sub

Any help would be much appreciated!

 

[John Big Booty]

I recently had a problem using the DLookup() function with dates, perhaps the discussion here will help you solve your problem.

 

[james_IT]

I recently had a problem using the DLookup() function with dates, perhaps the discussion here will help you solve your problem.

thanks for the response. i've had a look but still struggling...

 

[james_IT]

please see attached example db. really would appreciate any guidance. thanks

 

[james_IT]

can anyone help? im at a dead end - tried a few things to no avail