Using a table for parameter data and wildcards 1

[Molby]

Hi all,

What I'm trying to do is feed data from a table that has data loaded via a macro from Excel. What I then need to do is to feed this data into a query as a parameter, but at the same time use wildcard characters.

The problem is when I use ClientName (which is the column from the table the data is loaded into) with wildcards, I don't get any data returned. Basically I want to use all of the data loaded into the ClientName column to see if it is contained anywhere in the Actor Name column of the Client Data table.

Is it even possible to do what I'm after? I know that you can use a form to feed in parameters, but can you use a table in this way? I post the SQL below:

SELECT [CLIENT DATA].[Actor Name], [CLIENT DATA].[Primary Account], [CLIENT DATA].[Processing Site], [CLIENT DATA].Office, [Tbl-NamesQuery].ClientName
FROM [Tbl-NamesQuery] INNER JOIN [CLIENT DATA] ON [Tbl-NamesQuery].ClientName = [CLIENT DATA].[Actor Name]
WHERE ((([CLIENT DATA].[Actor Name]) Like "*" & [ClientName] & "*"));

Thanks in advance.

 

[hneal98]

I can see a problem with the WHERE clause right off.

SELECT 
    [CLIENT DATA].[Actor Name], 
    [CLIENT DATA].[Primary Account], 
    [CLIENT DATA].[Processing Site], 
    [CLIENT DATA].Office, 
    [Tbl-NamesQuery].ClientName
FROM 
    [Tbl-NamesQuery] 
    INNER JOIN [CLIENT DATA] ON [Tbl-NamesQuery].ClientName = [CLIENT DATA].[Actor Name]
WHERE 
    ((([CLIENT DATA].[Actor Name]) Like "*" 
    AND [ClientName] Like "*"));

You had [CLIENT DATA].[Actor Name]) Like "*" & [ClientName] & "*". You can see how I changed it. That should work.

 

[Golom]

Yes you can ... but it won't have any effect in the query you posted because the ON condition does matching on the equality of the two fields that appear with a LIKE operator in the WHERE clause.

The ON condition will select only those records that have

[Tbl-NamesQuery].ClientName = [CLIENT DATA].[Actor Name]


by definition ... if a field is equal to another field then it is also LIKE that field. (The opposite is not true however ... fields that are LIKE each other are not necessarily equal.)

 

[Molby]

Nope that isn't it. I need the value from ClientName to be used to query the column Actor Name, with wildcards around ClientName. What you have done is to use "*" in Actor Name and ClientName separately.

 

[Molby]

Thanks Golom, What should I use instead of "On" then?

 

[Molby]

Don't worry, I figured it out:

SELECT [CLIENT DATA].[Actor Name], [CLIENT DATA].[Primary Account], [CLIENT DATA].[Processing Site], [CLIENT DATA].Office, [Tbl-NamesQuery].ClientName
FROM [Tbl-NamesQuery],[CLIENT DATA]
WHERE ((([CLIENT DATA].[Actor Name]) Like "*" & [ClientName] & "*"));