Using wildcards past character limit

[NZArchie]

Hi guys I'm importing from a computer generated form which is limited to 20 characters per field, and matching these results to my current table, which often has more than 20 characters. How can I use wildcards to ensure that "Lifetime Christchurc" matches to "Lifetime Christchurch" in my database? (extra h on end)

The problem is that the field is not always 20 characters long and padded with white space, it could also be "Rob Dunn"

 

[Brianwarnock]

Does using some form of
Like [formfieldref] & "*"
not work

Brian

 

[NZArchie]

No sorry I can't get it to register, because I don't know the syntax to integrate with a field name. Currently I have:

LEFT JOIN Advisors ON ([TempTable-SovImport].AgentName = Advisors.FirstName) AND ([TempTable-SovImport].AdvisorLastName Like Advisors.LastName & "*")

Any tips?

 

[Brianwarnock]

This

([TempTable-SovImport].AdvisorLastName Like Advisors.LastName & "*")

is correct syntax so if there is a problem it is elsewhere.

I presume that the full From clause is

From [TempTable-SovImport] LEFT JOIN Advisors ON ([TempTable-SovImport].AgentName = Advisors.FirstName) AND ([TempTable-SovImport].AdvisorLastName Like Advisors.LastName & "*")

Brian

 

[NZArchie]

Ok it's got me stumped. Can you spot an error in this?

INSERT INTO Advisors ( FirstName, LastName )
SELECT DISTINCT [TempTable-SovImport].AgentName, [TempTable-SovImport].AdvisorLastName
From [TempTable-SovImport] LEFT JOIN Advisors 
ON (([TempTable-SovImport].AgentName Like (Advisors.FirstName & "*")) 
AND ([TempTable-SovImport].AdvisorLastName Like (Advisors.LastName & "*")))
Where ((Advisors.FirstName Is Null) AND (Advisors.LastName Is Null));

 

[Galaxiom]

The problem is you are doing a Like comparison where the shorter imported string is being compared with the longer string plus wildcards. You won't get a match if the import is truncated.

You need to reverse them

Advisors.FirstName Like ([TempTable-SovImport].AgentName & "*")

Or use the truncated version of the existing data.

[TempTable-SovImport].AgentName = Left(Advisors.FirstName, 20)