Filtering a form with a combo box using left function

[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

 

[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

 

[kc-mass]

Look at the "Like" operator in the help file.
"Like [1-9]"

Regards

Kevin

 

[ghetto_banjo]

try using the IsNumeric Function.

me.filter = "IsNumeric(Left(IDProcedure,1))

 

[joelgeraldine]

yytyyttrrrr

"jean" <[email protected]> a écrit dans le message de groupe de
discussion :
(e-mail address removed)...