filter by starts with number

[tagteam]

I am sure this is easy but I am spacing on how I would use a filter by form to find any records where the field value starts with a specific number. If it were text I would use like "72*" but it is a number so I am not sure how to do it. The values in the field can range between 4 and 6 characters.

[Welshgasman]

Use Left() and Cstr() ?

[ranman256]

add another field to the query where you convert the field to string: cStr([numField])
then use your filter : like "72*" in THAT field.

[moke123]

It depends on the datatype of the field. A text field can contain a number and needs delimiters, but a number field cant contain text.
Is it all numbers or text also?

[Welshgasman]

Use Left() and Cstr() ?

Do not even need the cstr(), but test anyway.

? forms!frmDailyEntry!sfcsfrmDaily.form.dailyid
 3084 
? left(forms!frmDailyEntry!sfcsfrmDaily.form.dailyid,1)
3
? left(forms!frmDailyEntry!sfcsfrmDaily.form.dailyid,1)=3
True

[tagteam]

I am trying to figure out a way to do it in the filter by form method. The user clicks filter my form and then enters a value in the box, which is numeric type.

[Welshgasman]

strFilter = "Left(FieldName,1) = " & Me.WantedNumber 
Debug.Print strFilter
Me.Filter = StrFilter
Me.FilterOn = True

[June7]

For FilterByForm use a query as form RecordSource.

Calculate a field to convert number field to string. User can enter 72* and Access will change to LIKE 72*.

I have never used FilterByForm in a deployed database. Would have to educate users on its use.

[CJ_London]

add another field to the query where you convert the field to string: cStr([numField])
then use your filter : like "72*" in THAT field.

ni need to convert the number to text, just use

like ‘72*’

[Welshgasman]

ni need to convert the number to text, just use

like ‘72*’

There are those smart quotes again.

[June7]

Okay, tested C.J. suggestion and it works on number field without converting to string in RecordSource. User has to know to enclose in apostrophes or quote marks (either works).