How do I make a contains query

[cayham86]

Hey guys,

I am trying to make a query which contains certain information so wondering how do I write a contains query?

 

[theDBguy]

Use the Like criteria?

 

[cayham86]

Use the Like criteria?

Tried that and it didn't work as it needs to contain part of a bit of text which isn't at the start of the description but in the middle of the description

 

[arnelgp]

you can also try Instr() function, eg:

Select * From yourTable Where Instr(1, [yourField], "theTextToFind") <> 0

 

[cayham86]

Isn't that being done directly from SQL not access

 

[arnelgp]

Instr() is an Access function and I've used that many times in Access Query.
there is no harm on Testing it.

btw Like will work, why did it not?

select * from yourTable where [field1] Like "*yourText*"

 

[cayham86]

Instr() is an Access function and I've used that many times in Access Query.
there is no harm on Testing it.

btw Like will work, why did it not?

select * from yourTable where [field1] Like "*yourText*"

Like didn't bring back any results

 

[arnelgp]

Like didn't bring back any results

show your SQL and show the data you want to show.

 

[cayham86]

show your SQL and show the data you want to show.

Ill have another try later

 

[Gasman]

Ill have another try later

I really cannot see why Like *text* will not work?

SELECT Transactions.*, Transactions.Description
FROM Transactions
WHERE (((Transactions.Description) Like "*ill*"));

 

[Pat Hartman]

If Instr() works then Like will also work.

 

[Galaxiom]

I am trying to make a query which contains certain information

Best avoid using the word "contains" in this context. CONTAINS predicate is used in SQL Server and not supported by Access/ACE which doesn't have Full Text Indexing.

Like or InStr() should definitely do the job. You really should show the actual SQL you used.
Could you be getting the search criteria out of another field or control and not concatenating it properly?