search on field with different input formats

[Lhuffst]

My search form allows users to search on license plate number. Because of vanity plates etc, there isn't a set pattern. Some have spaces, some do not. Since there are several search fields available, I build the sql. Here is the line for the license plate

 ssql = ssql + " tblParkingPermitVehList.fldLicenseNo = '*" & fldLicenseNo & "*' And "

Note. The and is stripped off if its not needed.

The search works if you put it in exact but if the value isn't stored in the database that way it doesn't return the record.
i.e. fls179 in the database is fls 179. If I enter fls179 on the form, I don't get any records; if I enter it as fls 179 it returns the record.

Since the database values are stored in a variety of ways, I need my sql to find it either way. Thanks lhuffst

 

[dhookom]

Don't use "=" since it expects exact match of the values.

ssql = ssql + " tblParkingPermitVehList.fldLicenseNo Like '*" & fldLicenseNo & "*' And "

Also, do you understand the difference between using "+" and "&"?


Duane
Hook'D on Access
MS Access MVP

 

[Lhuffst]

duane, thanks for the post. First...based on your response, I changed my + to a &..
Second - in the code, I had tried like before and it didn't work but your post make the lightbulb go off. I changed the code to

ssql = ssql & " tblParkingPermitVehList.fldLicenseNo Like '" & left(fldLicenseNo,3) & "*' And "

My reasoning was that the first 3 digits will always be digits and not spaces. So far the testing works fine.
Thanks for the help.

 

[Andrzejek]

"search on license plate number" and "the first 3 digits will always be digits and not spaces"

If you deal with license plate numbers, you cannot make that assumption. Even if your State (or Country) have the rule of "the first 3 digits will always be digits and not spaces", there always will be visitors from other places (right?) or 'personalized plates'

Have fun.

---- Andy