like operator

[biscuit72]

I need to write a query that searches a field that contains *'s

I tried using the Like operator with the Ascii chr(42) but can not get the right results as it seems to return all records

SELECT tbl_catalog.upc, tbl_catalog.Caption
FROM tbl_catalog
WHERE (((tbl_catalog.Caption) Like Chr(42)));

Thoughts?

Thanks

 

[dcx693]

The problem is that Chr(42) = * which is used as a wildcard character in Access (and many other computer-related instances). Use something like this instead:
WHERE (((tbl_catalog.Caption) Like "*" & "*'s";

Not sure if that syntax is right, but the idea is to use the "*" character to represent anything, then to use the "*'s" to represent the end of the field. Concatenate them using the & operator and it will find anything that ends with "*'s".

 

[biscuit72]

Actuallly I need to search for just a '*' not *'s in any part of the field not just at the end

Thanks

 

[dcx693]

OK then, much easier. If the * can occur anywhere in the field, use this:
WHERE tbl_catalog.Caption Like "*" & "[*]" & "*"

You need to place [ and ] characters around special characters when you search for them using Like.