Date filters using combo box

[Neilster]

Hi guys

I have a set of combo filters that filter one after the other as follows –

Private Sub cboCity_AfterUpdate()
    If Nz(Me.cboCity.Text) = "" Then
        Me.Form.Filter = ""
        Me.FilterOn = False
    ElseIf Me.cboCity.ListIndex <> -1 Then
        Me.Form.Filter = Me.Form.Filter & " and [City] = '" & _
        Replace(Me.cboCity.Text, "'", "''") & "'"
        Me.FilterOn = True
    Else
        Me.Form.Filter = Me.Form.Filter & " and [City] = '" & _
        Replace(Me.cboCity.Text, "'", "''") & "'"
        Me.FilterOn = True
    End If
    
    Me.cboCity.SetFocus
    Me.cboCity.SelStart = Len(Me.cboCity.Text)
End Sub

and so on to filter down so the user can work with what they filter, my question is how can I add on a filter that filters between dates? and second I was hoping that I could program the filters so that they could be changed individually/randomly as to filtering one after the other and then clearing to restart the filtering again if that makes sense.

I have tried using this, but it doesn’t work ‘Bad Command’

Me.Form.Filter=”StartDate =#” & Format(Me.txtStartDate, “mm/dd/yyyy”) & “#”

And

Me.Form.Filter=”EndDate =#” & Format(Me.txtEndDate, “mm/dd/yyyy”) & “#”

Cheers guys!

 

[pr2-eugin]

Hi, Please use Code Tags when posting VBA Code

Regarding your Query, you need to generate the Filter using the two fields combined..

Me.Form.Filter = "StartDate >= " & Format(Me.txtStartDate, "\#mm\/dd\/yyyy\#") & _
                 " AND EndDate <= " & Format(Me.txtEndDate, "\#mm\/dd\/yyyy\#")

 

[Neilster]

Hi

How does that work with the code that I am currently using, and would that vba be used for 2 fields on the after_update event?

Thanks

 

[pr2-eugin]

I would suggest you do not rely upon the after update event, but have a button that will do the filter in one go. As specially with this date field, how will you be able to apply the two conditions? You will not, it would either be > or < not the two together.

 

[Neilster]

Hi

That's OK which I have used before, however it just filters all dates for every employee as opposed to filtering that specific date range for that specific employee.

What I was hoping to do is once that employee has filtered down what criteria they want to work with, they can then filter between dates that will filter just the date range in their name.