DLookup - wildcard search on both sides.

[mdnuts]

When trying to match people's names - sometimes with a middle initial, sometimes without - I'd like to try to wildcard each side of it.

So this

varProblem = DLookup("EMAIL_ADDRESS", "tblUsers", "EMPLOYEE_NAME Like '" & varString & "*'")

but also something like this.

varProblem = DLookup("EMAIL_ADDRESS", "tblUsers", "EMPLOYEE_NAME* Like '" & varString & "'")

Has anyone tried something like this before?

 

[MarkK]

Wild card characters go after the word LIKE, and between your single quotes.

"EMPLOYEE_NAME Like '*" & varString & "*'"

 

[mdnuts]

Thank you, that wasn't what I was asking.

In other words,
tblPhone may have a user named Smith, George B

and if I try to compare it against tblUsers who also has him but listed as Smith, George it won't match. An easy way to think is to do it in reverse but not possible as i'm trying to look it up to start with.

 

[spikepl]

The wild-card bit applies only to the value you try to match to a variable, not to the variable itself. So no, you can't do what you want.

 

[MarkK]

If you have one discrete object called Smith, George, he should not exist in two different tables such that the spellings of his name may differ. Which one is authoritative?

If you control both tblUsers and tblPhone, consider adding a tblPerson, and then linking to the person using a long integer key field. This will be far simpler and more reliable than the text matching scenario you describe.

But as to the wildcard matching you are asking about, you could try a where clause like . . .

"'" & varString & "' LIKE (EMPLOYEE_NAME & '*')"

I haven't tried something like that before, but if you do, I would be curious to hear if you can make it work.

 

[vbaInet]

If you have one discrete object called Smith, George, he should not exist in two different tables such that the spellings of his name may differ. Which one is authoritative?

If you control both tblUsers and tblPhone, consider adding a tblPerson, and then linking to the person using a long integer key field. This will be far simpler and more reliable than the text matching scenario you describe.

I concur with MarkK!

You should be matching against a UserID of some sort because there could more than one Smith, George and the DLookup() wildcard search will only return the very first instance.

If you're unable to use or devise a UserID for whatever reason, then you could consider matching against more than just the one field. I.e. postcode, dob etc.

 

[Galaxiom]

Try the function in post 10 of this thread.

It will give you a value for the difference between the two strings. Order By the value returned from the function and the nearest matches will be at the top.

 

[mdnuts]

If you have one discrete object called Smith, George, he should not exist in two different tables such that the spellings of his name may differ. Which one is authoritative?

If you control both tblUsers and tblPhone, consider adding a tblPerson, and then linking to the person using a long integer key field. This will be far simpler and more reliable than the text matching scenario you describe.

But as to the wildcard matching you are asking about, you could try a where clause like . . .

"'" & varString & "' LIKE (EMPLOYEE_NAME & '*')"

I haven't tried something like that before, but if you do, I would be curious to hear if you can make it work.

yeah, I don't control both tables, only one. the other comes in on a regular basis that I import.

i tried the example - it just gave a null value in return.

 

[mdnuts]

Try the function in post 10 of this thread.

It will give you a value for the difference between the two strings. Order By the value returned from the function and the nearest matches will be at the top.

Now that's interesting. I ran it against my example of Smith, George B.

Using Smith, George B as the input against the table that had it as Smith, George it showed a score of 1.

Matching exact name against exact name of course scores 0.

If I match the other way around it shows me 2.2. Which makes sense as it's removing the middle initial and the space.

I suppose I could get it to pop up the 5 best matches and see what happens from there.