multiple criteria for one field

[Khalil Handal]

Hi ,


I have this query and I obtain results from it.

SELECT FieldName FROM TableName WHERE FieldName Like "*Word 1*";

I am trying to add another criteria to the same field. FieldName Like "*Word 1*" AND Like "*Word 2*" with out success.

In other words I want to search for records with both criteria. The table represents the many side of a one-to-many relationship.
Any ideas ?

Khalil

[Welshgasman]

So that would be OR not AND ?
You say both criteria, but I am assuming either of both criteria.? How can the fieldname be Word1 AND Word2? One negates the other?
Or just search for Word?

[ArviLaanemets]

SELECT FieldName From TableName WHERE FieldName IN (Name1; Name2; ...; NameN)

, but I'm almost sure you can't use LIKE there.

[Khalil Handal]

Thanks
Is there a way to use the Like?

[Gicu]

Yes, using the OR operator instead of AND as explained by Welshgasman. You could also look at the Instr() function:

Instr([YourField],"Word")>0

Adding the above calculated field to your query with True in the criteria row would return all records containing "Word" in that field.
https://support.microsoft.com/en-us/...8-77cd0cb8a55b
Cheers,

[June7]

So given a data sample like:

ID	Key_FK	Word
1	1	Word1
2	1	Word2
3	1	Word3
4	2	Word1
5	2	Word2
6	3	Word3
7	3	Word4
8	4	Word1
9	5	Word2

You would want to retrieve only Key_FK 1 and 2 because those Key_FK are associated with both Word1 and Word2?
Selecting records based on data in another record of same table is never simple.

Do you want to return the entire record or just the Key_FK? Would you want to return record ID 3 along with ID 1 and ID 2?

Consider:
SELECT Words.* FROM Words
WHERE Key_FK In (SELECT Key_FK FROM Words WHERE Word LIKE "*Word1*") AND Key_FK IN (SELECT Key_FK FROM Words WHERE Word LIKE "*Word2*");

also:
SELECT Words.* FROM Words
WHERE (((Words.Word) Like "*Word1*" Or (Words.Word) Like "*Word2*") AND ((Words.[Key_FK]) In (SELECT Key_FK FROM Words WHERE Word LIKE "*Word1*") And (Words.[Key_FK]) In (SELECT Key_FK FROM Words WHERE Word LIKE "*Word2*")));



Now, assuming the Word value will not be repeated for each Key_FK, consider:
SELECT Words.Key_FK, Count(IIf([Word] Like "*Word1*" Or [Word] Like "*Word2*",1,Null)) AS Cnt
FROM Words
GROUP BY Words.Key_FK
HAVING (((Count(IIf([Word] Like "*Word1*" Or [Word] Like "*Word2*",1,Null)))=2));

Anything more complicated than 2 words should probably be handled with a custom VBA function.

[Khalil Handal]

Thank you for he reply. I will try it and keep you posted.
Khalil