Filtering a form with a combo box using left function

J

jean

Hi

I have a table named "TblData" containing 5 field

One field is named "IDProcedure" (text field)

This field contains information like "M-41-25" or "P-1000" or simply
"41-50"

I have a form with "TblData" as records source

As I want to filter the form, I create a combo box with 3 choices
"Manuals"; "Procedures" and "Process"

Now I have to put some code on the after update of the combo box
"ChoixCat" to filter the form

The only way I can manege it is using the first left character of the
field "IDProcedure"

So if the user select "Manuals", I want the filter to be applied to
all records starting with the lette "M"
if the user select "Process" I want the filter to be applied to all
records starting with the letter "P"
And finaly if the user select "Procedures" The filter should be
applied to all records starting with a number (from 0 to 9)

I have start with and if statement like

If [ChoixCat] = "Manuals" Then

Me.Filter = "left([IDProcedure], 1) = 'M'"
Me.FilterOn = True

End If

If [ChoixCat] = "Process" Then

Me.Filter = "left([IDProcedure], 1) = 'P'"
Me.FilterOn = True

End If

If [ChoixCat] = "Procedures" Then

Me.Filter = "left([IDProcedure], 1) >=0"
Me.FilterOn = True

End If

I cannot find the right syntax for the last if statement. It should
filter all records where the IDProcedure start with any number
including 0

thanks for helping
 
J

John Spencer

Try using Like with a wildcard

Me.Filter = "[IDProcedure] LIKE '[0-9]*'"

You could use similar logic with your other filters
Me.Filter = "[IDProcedure] Like 'M*'"
Me.Filter = "[IDProcedure] Like 'P*'"

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top