storing string data

[Apollo13a]

Hi, I've created a material pricing Db that holds material name, category and prices from different suppliers along with the price date. The user selects a category from a combo and I run some vba SQL code to fill a second combo that shows all material in that category, the user can then select material from the second combo or add new material via the NotInList event. My question is: How do I handle the user typing in(the material combo box) various data that describes the same material, ie, 1/2" emt or 1/2 " emt or 1/2 "EMT or 1/2"EMT. I'm thinking that using the "like" operator in my query will help. Although I'm posting just before tackling this problem so I don't really know. Any solutions would be helpful.
Thanks Jim G.

 

[JerryKlmns]

Apollo13a

You could use
*A proper case function thread181-749486
*The Replace function to get rid of single or/and double quotes plus double spaces
*The Trim function to get rid of leading and trailing spaces

 

[bboffin]

Yes you can use the LIKE operator and you should prefix and suffix the user's entered value with * and replace all spaces with * as well.

So if the user entered "1/2 em" you should convert this to
"*1/2*em*" by something like:
"*" & Replace(Trim(lUserValue), " ", "*") & "*"

Be aware that using LIKE may produce multiple hits and you will need to deal with this.

You don't need to worry about the case of the text as by default Access is case insensitive during queries.



Bob Boffin

 

[JerryKlmns]

Bob,

Access is case insensitive during queries but printed reports should be also "boss" insensitive! A hard thing to find