Like a combobox

[steve1111]

Hi,

I have a combo box in a navigation form that has a list of Vendors users enter in another table. I want to pull up all the records that are Like that cbx. For example one vendor might be Jiffy Lube, but users have entered by error, Jiff Lube or Jify Lube. Is there a way to get all the like Jiffy Lube(s) or whatever the cbx is reading?

I am using Like "*[Forms]![frmMain_toplg]![SubForm1].[Form]![NavigationSubform].[Form]![cbxVendorIA]*"

the path is correct. Thanks

 

[plog]

'Jiffy Lube' isn't like 'Jiff Lube', neither of which are like 'Jify Lube'. So no.

'Jif' is like all of them, as is 'Lube'. 'Jiff Lube' and 'Jify Lube' contain all of their characters in the same order as 'Jiffy Lube', but not each other. And on and on. I'm sure this some complex code out there that can assign a 'matching score' between 2 strings and then allow you to see only those above a certain matching score.

But the correct way to do this is to stop letting users enter data for this field. Instead they need to select it from a drop down so that the same vendor is always in the data in the same way.

 

[Isskint]

Oh Plog is so right on that! It is the best option. There are other ways but that requires a lot of coding; EG

• Pattern matching
• Seperate table listing every alternative spelling you can think of for each vendor
• Soundex matching

 

[steve1111]

Thanks for the feedback guys, i guess the way of a drop down without limiting to list will have to be the best way to go. Do you recommend then just a Find/Replace in the table for the initial clean up?

 

[steve1111]

One other thought i wanted to get your opinion on, what about a hidden text box that uses Left(,2) to grab the start of the cbx and then can the query run off the hidden text box? but then that puts me back to my initial question of the like syntex would be correct looking for a value in a control?

 

[plog]

No need to make it a hidden input, just use your existing combo box. Your error is because you are searching for that big string in your data, you are not using it as a variable as you wanted.

To use it as a variable you need to seperate it from the string portion:

Like "*" & [Forms]![frmMain_toplg]![SubForm1].[Form]![NavigationSubform].[Form]![cbxVendorIA] & "*"

Slap the Left() function around the variable.

 

[steve1111]

Not sure that i did that recommendation right, still was not filtering, but because i am still learning Access i will go with the simpler cbx for now. thanks again plog

 

[steve1111]

Follow up, plog's suggestion is correct and works, i had an additional error elsewhere in the query.

 

[Galaxiom]

I'm sure this some complex code out there that can assign a 'matching score' between 2 strings

Yes. It is called the Damerau-Levenshtein distance.

Have a look at the function in Post 10 of this thread.

It is a really useful function. I have used it extensively for finding typographical variations in data.