Using Me.Filter to filter a form.

[chrisrigoni]

Hey, I am a novice with coding for Access 2007. I am trying to filter a form using Me.filter. Here is the code I'm using.

Option Compare Database
Private Sub Command34_Click()
Me.Filter = "*" & [Name] = "*" & Me.Text32
Me.FilterOn = True
Me.Requery
 
End Sub
Private Sub Command35_Click()
Me.FilterOn = False
Me.Requery
End Sub
Private Sub Command36_Click()
Me.Filter = "DptCd =" & Me.Text32
Me.FilterOn = True
Me.Requery
End Sub

There are two problems. I want to be able to just type in any part of the name and bring up all profiles that contain that part. That way I can just type last name and bring up all profiles with that last name. Also, the DptCd is a numeric value and I am getting a "Data type mismatch in criteria" error when I try to filter using it. I have a textbox set up for input and three buttons: Filter Name, Filter DptCd, and Unfilter. Thank everyone for their help!

 

[HiTechCoach]

Try:

Option Compare Database
Option Explicit ' <<< good to add this


Private Sub Command34_Click()
   ' for anything starting with the input value
   Me.Filter = "[Name] Like " & Chr(34) & Me.Text32 & "*" & Chr(34) 
   Me.FilterOn = True
   Me.Requery
 
End Sub

Private Sub Command35_Click()
   Me.Filter = ""   ' clear filter
   Me.FilterOn = False
   Me.Requery
End Sub


Private Sub Command36_Click()
    Me.Filter = "DptCd =" & Me.Text32
    Me.FilterOn = True
    Me.Requery
End Sub

 

[chrisrigoni]

Thank you for your quick response. Now I get a syntax error in the line:

Me.Filter = "[Name] Like " & Chr(34) & Me.Text32 & "*" Chr(34)

 

[HiTechCoach]

Try:

   Me.Filter = "[Name] Like " & Chr(34) & Me.Text32 & "*" & Chr(34)

 

[chrisrigoni]

Thank you so much! It worked perfectly. I really appreciate your help!

 

[HiTechCoach]

You're welcome!

Glad we could assist.

 

[chrisrigoni]

Okay, unfortunately, I am having another problem. For the DptCd, I am still getting an error of "Data type mismatch in criteria expression" when I try to filter by the department code. It is a numeric value. I created another text box and changed the Format option to "General Number," but I still get the error. Here is the code:

Option Compare Database
Option Explicit ' <<< good to add this

Private Sub Command34_Click()
   ' for anything starting with the input value
   Me.Filter = "[Name] Like " & Chr(34) & Me.Text32 & "*" & Chr(34)
   Me.FilterOn = True
   Me.Requery
 
End Sub
Private Sub Command35_Click()
   Me.Filter = ""   ' clear filter
   Me.FilterOn = False
   Me.Requery
End Sub

Private Sub Command36_Click()
    Me.Filter = "DptCd =" & Me.Text37
    Me.FilterOn = True
    Me.Requery
End Sub

Thanks so much!

 

[HiTechCoach]

What is the data type for the field int he table. My guess is that it si text and not a numeric data type. If this is true while you may be typing in a number, it is still stored as text.

Try:

Me.Filter = "DptCd =" & Chr(34) & Me.Text37 & Chr(34)

If the above works, then the data type in the table probably is not numeric.

 

[chrisrigoni]

It did work. Thank you very much! It isn't numeric.