Search for "contains" works like "equals"

B

BlueWolverine

Hello,
MS Access 2003 on XP Pro.

I have a series of daisy chained queries to count Codes for me.

I have entries in MASTER that may have multiple comma separated codes in one
field. I have these queries to look through that cell and find out what
codes are in those cells.

Now, I was told the SQL Like "*" & & "*" would take care of that for
me. But it's only grabbing ='s right now.

So it's like this.
I have ten entries that actually contain B64. I know this because a search
query I wrote found 10 containing the 3 character string "B64". However, the
join query using LIKE instead of = returns only the 2 entries that equal B64.

q_CCC_Uniques is the list of all unique codes (no repeats on this list at
all.)
[QMC MASTER LIST] contains a field [CCC's] that contains comma separated
list of CCC codes. for instance, one entry in [CCC's] might look like this.
A14, B64AT, B63AB, L26.

That should be FOUND for B64.

Here is the SQL of the query where it breaks down.

SELECT [QMC MASTER LIST].[Work Element Description], [QMC MASTER LIST].[GQRS
CAT], [QMC MASTER LIST].ACTION, [QMC MASTER LIST].[CCC's], [QMC MASTER
LIST].ACTIVE
FROM q_CCC_Uniques INNER JOIN [QMC MASTER LIST] ON q_CCC_Uniques.CCC Like
"*" & [QMC MASTER LIST].[CCC's] & "*"
WHERE ((([QMC MASTER LIST].Active)=Forms!f_Stats!StatActive))
ORDER BY [CCC];

How can I get this to work?

Thank you for your help in advance.
 
K

KARL DEWEY

I do not think you can based on the information presented.
You are joining [q_CCC_Uniques].[CCC] to [QMC MASTER LIST].[CCC's] and say
that [QMC MASTER LIST].[CCC's] would have data such as A14, B64AT, B63AB,
L26.

Now if [q_CCC_Uniques].[CCC] contains B64 your query would work if this was
the SQL --
SELECT [QMC MASTER LIST].[Work Element Description], [QMC MASTER LIST].[GQRS
CAT], [QMC MASTER LIST].ACTION, [QMC MASTER LIST].[CCC's], [QMC MASTER
LIST].ACTIVE
FROM q_CCC_Uniques, [QMC MASTER LIST]
WHERE ([QMC MASTER LIST].Active=Forms!f_Stats!StatActive) AND [QMC MASTER
LIST].[CCC's] Like "*" & q_CCC_Uniques.CCC & "*")
ORDER BY [CCC];
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top