Find records containing another field value 1

[TrekBiker]

In a Customers table there's a CompanyName and separate fields for the customer's FirstName and LastName. Sometimes there's no separate CompanyName, in which case the client wants to use [LastName, FirstName]. This will allow a lookup on the frmCustomers form to find anyone.

The existing Customers table is a mess, some records having [LastName FirstName] without a comma, others [FirstName] [LastName], etc To sort it I want to be able to find any CompanyName that contains either FirstName or LastName.

I feel it ought to be easy to use a query with the CompanyName criteria picking out the field values [FirstName] or [LastName].

For example

Like "*[FirstName]*"

But this applies no filtering at all. I'm having no luck searching FAQ etc.

 

[Andrzejek]

Let's say you are looking for records with [tt]John[/tt] in the three fields.
How about:

[pre]
Select CompanyName, FirstName, LastName
From Customers
Where CompanyName LIKE "*John*" OR
FirstName LIKE "*John*" OR
LastName LIKE "*John*"
[/pre]


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Thanks Andy, yes that would work for fixed 'John'.

This is an extract from the Customers table.

I want to be able to find all records like the three highlighted, Prime, Jones, Green. Some Customer Names may only have FirstName or LastName, some are without commas, some with names reversed, etc.

So the query criteria have to use the FirstName or LastName for each record.

 

[Andrzejek]

I see. So, what you want to do is something like:

[pre]
Select *
From Customers
Where [Customer Name] LIKE "*" & FirstName & "*" & LastName & "*"
OR [Customer Name] LIKE "*" & LastName & "*" & FirstName & "*"[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Thanks again but not getting what I want. This is my query

And this is what it produces

It's including records where FirstName and LastName are blank, rather than excluding them.

I've tried various criteria permutations but am not finding the answer.

 

[Andrzejek]

First, looks to me that you have fields' names of [First Name] and [Last Name] with Spaces in the names, but your criteria are FirstName and LastName, no Spaces.
Second, I would assume that the records with anything (blank?) in the FirstName and/or LastName fields are NULLs, and not empty strings/space(s)

If my second assumption is right, try:[tt]
(LIKE "*" & FirstName & "*" & LastName & "*" OR LIKE "*" & LastName & "*" & FirstName & "*")[blue]
AND (FirstName IS NOT NULL OR LastName IS NOT NULL)[/blue][/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Thanks again Andy.

Still not there but you have given me the syntax to be able to experiment with a few variations.

Just to clarify, the CompanyName field is a mess at the moment, with all permutations of FirstName and LastName including neither for Private customers, or a company name for trade customers.

What I'm aiming for is to be able to query this CustomerName field, whatever it contains, to find the records containing either FirstName or LastName, or both. I can then rebuild these so they're all consistently showing LastName, FirstName.

 

[Andrzejek]

So, checking for NULL in FirstName and LastName fields did not do the trick... :-(
When you find the solution, please share it here. Others may find it helpful.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

I'm afraid not, will investigate further and respond if I get a workable answer.

 

[Andrzejek]

You may want to do some cleaning of your data, like:
[tt]
Update Customers
Set LastName = NULL
Where Len(Trim(LastName)) = 0[/tt]

unless there are some unprintable characters in this field.... :-(

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson