Not like query

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

I have a list which includes several names which begin
with the asterisk character. For example:

***
*** School
*** Illness
*** Vacation
Job Assignment
Course Instructor
*** Time Off Personal
Travel

I am attempting to write a query to exclude anything in
the list beginning with three asterisks "***"

I have attempted to use literals and ANSI character set
chr(42)for * and chr(37) for % without success.

The criteria I have attempted:

Not chr(42) & chr(42) & chr(42) & chr(37)
Not "*" & "*" & "*" & "%"
Not (*) & (*) & (*) & (%)

Can anyone suggest the correct syntax for this criteria.

TIA
 
Dear Rick:

To place an otherwise wildcard character in a search string but use
its literal value, place it inside square brackets:

To exclude anything beginning with 3 asterisks:

NOT LIKE "[*][*][*]%"

or

NOT LIKE "[*][*][*]*"

The first is for MSDE or SQL Server, the latter for Jet databases.

However, asterisk is not a special character in the first case, so it
is sufficient to use:

NOT LIKE "***%"

Another way would be:

LEFT(FieldName, 3) <> "***"

When you don't use "LIKE" there are no special characters with which
to be concerned.

If in doubt, use more than one technique and compare the number of
rows returned.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top