Instr(1,YourField, Chr(38)) >0

mx

Example:

SELECT Table1.*
FROM Table1
WHERE (((InStr(1,[FIELD1],Chr(38))>0)=True));


mx

& is a system character used in combining values into strings but I believe you can isolate it by putting it in quotes. So your like phrase would be Like *& "&" &*

Or use the ASCII above from MX.  That is much cleaner.

Sorry MX...didnt see you answered before I posted.

>> I've tried 'Like *&*' and other variations without success.<<

.... Hmmm ....

I notice that in your post, you do NOT have quotes around your string .... the follow works just fine:

SELECT * FROM SomeTable WHERE SomeTextField Like "*&*"

Note ... you can use single quotes too .....

SELECT * FROM SomeTable WHERE SomeTextField Like "*&*"

Oh ... one more question/comment ... if you are using ADO (or a Passthru to a SQL Server back end) the wildcard character is % not * ...

So in SQL Server syntax (or ADO):

SELECT * FROM SomeTable WHERE SomeTextField Like '%&%'

Thanks!

??  

The safest and most reliable way to make comparisons when special characters are used with InStr() - which ignores them.

mx

Hello MX ....

>> The safest and most reliable way to make comparisons when special characters are used with InStr() <<

Ok .... well .... what makes the ampersand a special character? ... The & is not a delimitor, nor is it a wild card character, or special delimiter ... the ampersand is just an operator character, so ... its no different than a plus, or minus is it? ... so ... no need to use InStr()

It's the concatenation character in Access.

mx

... Yes ... I know that ... but why does that make it a special character? ... how is it different than any another operator? ...

For example ... if you were searching for a plus (+) ...  or a minus (-) ... or a divisor (/) ... or a caret (^) ... would you use the InStr()? ... there is really no reason to, since the Like operator works just fine.

"how is it different than any another operator? ..."

It's not ... that's kind of my point.  Many times you will do this:

Like "abc" & "*"

                 ^^

It this case of this Q ... we are trying to find a & in some string.  InStr() makes this simple and avoids a load of quotes, double quotes or whatever.

mx

"would you use the InStr()? ..."

Yes I would ...

mx

>> Many times you will do this:

Like "abc" & "*"  <<

Yes that is true ... but how does your expression make the ampersand a "special" character? ... The concatenation of the the two values you have shown simply creates a text pattern to be searched for.   Your given expression will find all the records that start with "abc" ... if the expression is modified to be like this:

Like "abc&" & "*"

Access will find all records that start with "abc&" ... and if the expression is modified to be like this:

Like "abc+" & "*"

{Note the + is a concatenator too} Access will find all the records that start with "abc+".

.....

So ... following your logic with the concept that operators should be searched for with InStr(), then you would use InStr() when searching for the patterns "And", "Or", and "Like" too? ... Using InStr() for those operators (as well as the ampersand), although valid, is a detriment to efficiency of the execution of the SQL statement (possibly neglibible, but still a reducer of efficiency), due to the needless call to VBA's InStr().

...

Out of curiousity, can you show me where an operator (other than those that double as wild cards, like the asterick) can cause an issue with the Like operator pattern comparisons? {Note: I said operator, NOT wild card characters or text delimitors :) }

The characters that I would utilize the InStr() for are the ones that are used for pattern expressions, wild cards, or text delimitors {parenthesis used to delimit the character, not to indicate a cadidate for InStr()}:

(?) or (_)  means any single character.

(*) or (%)  means zero or more characters in the spot its used.

(#)  means any single digit (09).

([)charlist(])  means any char in the char list within the square brackets (The pair is killer here).

(') or (")  delimits text, so the SQL interpreter maybe looking for its mate.  Using "'" (double, single, double) or '"' (single, double, single) is fine ... but trying to use a single in a string that is delimited by singles is no good, just as a double in a string delimited by doubles.  When a text delimitor is encapsulated by the same delimitor, you can repeat the delimitor being sought.  For example: Like "ab""" & "*" will return all records that start with (ab").  Another method is to encapsulate the special character in square brackets ... Like "ab[*]*" will find all records that begin with ab<asterik>.

For more info on searching using the Like operator:

Like Operator (Jet SQL)
http://office.microsoft.com/en-us/access/HP010322531033.aspx

Using Wildcard Characters in String Comparisons
http://office.microsoft.com/en-us/access/HP010322841033.aspx

There are other links that can be followed on those pages ... but ... ulitmately, there is NO NEED for the InStr(), nor is there an increase in safety or reliability in using InStr(), when trying to match a pattern containing the ampersand.  Again, I ask you to show me a situation where the ampersand causes an issue (ie: is mistaken for an operator instead of part of a string pattern being searched for), I am certainly willing to eat a piece of humble pie if one can be presented.

---------
Please note, my mode of conversation during this post/thread is not intended to sound less than cordial or respectful .... I truly enjoy conversations of this nature and look at these types of discussions as opportunities to learn and/or teach, so ... just wanted to say this so you know that if my tone was being read as "a little on the offensive", please know that I am just talking .... not trying to toss attitude! ... :) .... {sometimes the written word can be so misunderstood with respect to tone}

I'm with datAdrenaline on this one; InStr() seems like overkill.