VBA Combo Filters

[Neilster]

Hi guys

I have a set of combo filters that filter one after the other as follows - If Not IsNull(Me.NameFilterBox) Then
If Me.Form.Filter="" Then
Me.Form.Filter="Name ='" & Me.NameFilterBox & "'"
Else
Me.Form.Filter = Me.Form.Filter & " and Name = '" & Me.NameFilterBox & "'"
End If
End If
If Not IsNull(Me.LocationFilterBox) Then
If Me.Form.Filter="" Then
Me.Form.Filter="Location ='" & Me.LocationFilterBox & "'"
Else
Me.Form.Filter = Me.Form.Filter & " and Location = '" & Me.LocationFilterBox & "'"
End If
End If


and the I use the

Me!Form.Filter = Me!Form.Filter & " and Name = '" & Me!cboOPOwner.Text & "'"

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

Cheers guys!

 

[namliam]

Please use code tags when posting code on the forum (see my signature for details)

1)
A filter on dates works the same as on a string, except you use #
... Somedatefield = #" & format(Yourdatefield,"MM/DD/YYYY") & "#"

THe format is needed because it needs to be in US format, this is the "only" way to make sure it will work.

2)
Well once they are on screen it should be simple for the user to change and simply run this code again.... should really run pretty fast.

 

[Neilster]

Sorry I meant this method changing city to startdate

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

 

[namliam]

Please use code tags when posting code on the forum (see my signature for details)

Sorry am not comprihending how would you change city to a date?

 

[Neilster]

As in I need to filter User - Status - City - Sector and then Next Call Date, but I need to filter between dates for Next Call Date so that the user can select a certain date range, therefore I have 2 text boxes one is startdate & enddate, so I was hoping the above vba could be used to filter dates.

 

[namliam]

yes you can and like I said, it works exactly the same as you do with text columns except you use # to wrap your value.

 

[Neilster]

Ok cool, so how would I place ( #" & format(Yourdatefield,"MM/DD/YYYY") & "#" )

that in the above vba?

 

[namliam]

Would you prefer I write the full database for you instead?

'for a text field
    Me.Form.Filter="Name ='" & Me.NameFilterBox & "'"
'For a date field
    Me.Form.Filter="Name =#" & Format(Me.NameFilterBox, "MM/DD/YYYY") & "#"
'For a number field:
    Me.Form.Filter="Name =" & Me.NameFilterBox & ""

 

[Frothingslosh]

Namliam - never ask a question to which you do not wish to hear the answer!

 

[Neilster]

I was only asking for a little help, you didn't have to reply! Again thanks for your help but it hasn't helped with my issue.

 

[namliam]

And I am trying to help, but your not helping yourself...

What about my post is unclear to you?

 

[Frothingslosh]

Just to clarify things a bit, you're basically looking for something kind of like what's done in the attached photo?