Access 97: using WHERE ... LIKE to compare user input string

[krizzotti]

I am trying to write a query that will search the database for a portion of a string that is input by a user.

The field I am looking at sometimes contains dashes, leading zeros, etc. so I want to use the LIKE as a contains because the user does not know exactly how it was entered.

So I want to know if the billnum contains 8836 - it may be entered as 08836, a8836, 8836-a, etc. I can find it if I enter it exactly as it is in the database with the following line:

WHERE (Claims.BillNum1)=[Forms]![FindProNumber]![BillNumber]

but I can't find a way to have it look for the string in the field. Maybe it can't be done in Access or I have to use VB?
I tried:

WHERE (Claims.BillNum1) like ‘*[Forms]![FindProNumber]![BillNumber]*’) 

- didn't get a syntax error, but it's not finding my record.
Any help would be greatly appreciated!

Kathy

[jimatqsi]

You could use

WHERE Instr(Claims.BillNum1,[Forms]![FindProNumber]![BillNumber])>0

That call to Instr will return the character number where the string begins if it is in the searched string; or it returns zero if it's not.

Jim

[zmbd]

The wildcard will be your friend and you ALMOST have it here:
WHERE (Claims.BillNum1) like ‘*[Forms]![FindProNumber]![BillNumber]*’)
issues:

1. the "smart" single quote, you can't use these in the SQL these need to be the normal " ' " dumb-style.
2. the extra quote shouldn’t be needed at all using the “Like” comparator.
3. the name of the control is being returned, not the value.

So instead of the expected:
WHERE (Claims.BillNum1) like *ControlValue_String*)
the string being returned is:
WHERE (Claims.BillNum1) like ‘*[Forms]![FindProNumber]![BillNumber]*’)


So try this little tweak (note this is air code):
WHERE ((Claims.BillNum1) Like "*" & [Forms]![FindProNumber]![BillNumber] & "*")
Note: no single or extra double quote and the placement of the ampersand ( & ) and the remaining portion of the SQL/code.

Be careful with the wildcard... if the "*" throws an error then you maybe using the alternate standard available in access where the "%" sign is used instead. I don’t remember which version; however, MS went from DAO to ADO and then back again and the ADO uses the "%" sign:
Access wildcard character reference. So if you get an error you should know how to fix it :)

[zmbd]

Looks like I cross posted just a little...
For most applications, I would avoid using the INSTR() function over the "Like" operator. Generally, I avoid all functions in SQL if there is an operator that performs the same or similar action on the data.
The function requires that the VBA environment is available whereas the operator is part of the SQL engine.

In any case the search may take a long time as the database grows.

Here's a link for that covers the three most common ways of searching within string:
ACC: How to Search for Characters or Strings in Records

[krizzotti]

omg - it worked perfectly! Thank you so much!