Criteria to Select "*" but exclude "?"

  • Thread starter Thread starter PlarfySoober
  • Start date Start date
P

PlarfySoober

I have been successful in selecting records in a table that bear the word,
"manager" in one field.

I also need to exclude records in the same table that bear the word,
"nurse", in the same field.

Nothing like, <Like "manager" AND NOT Like "Nurse"> has worked, and clearly
I don't know the syntax for this selection criterion.

If this is clear, could somebody make a suggestion?

Thanks.

Don.
 
I have been successful in selecting records in a table that bear the word,
"manager" in one field.

I also need to exclude records in the same table that bear the word,
"nurse", in the same field.

Nothing like, <Like "manager" AND NOT Like "Nurse"> has worked, and clearly
I don't know the syntax for this selection criterion.

If this is clear, could somebody make a suggestion?

Thanks.

Don.
Click to expand...

The LIKE operator accepts wildcards. If you don't use wildcards, it does an
exact match, just as if you had used the = operator. Are you looking for
records where a text field contains a longer string which might or might not
contain "manager" or "nurse" as a substring? or does the field contain just
the single word "manager"? If it does, then all you need is

= "manager"

as a criterion, since this will obviously not find records where the field is
equal to "nurse".

If it's a longer field, try

LIKE "*manager*" AND NOT LIKE "*nurse*"

This will *exclude* records where the field is, say,

This manager is in charge of ten nurses and 22 other support staff

That may not be the desired effect!

More info please!
 
Um, if you're only interested in records that have Manager in the field,
won't that automatically eliminate the records with Nurse in the field?

Or are you saying that the field can have multiple words in it, and you want
those records that have Manager somewhere in the field, but not if they also
have Nurse somewhere in the field?

Like "*manager*" And Not Like "*nurse*"
 
Don

If you don't include wildcards in your Like statement, Access looks for an
exact match.

Are you saying that the field can hold MORE than one value at a time, or can
ONLY be either "manager" or "nurse"?

Or are you saying that the field can hold multiple values at once (e.g.,
"manager" AND "nurse" AND ...)?

If the latter, it might be time to revisit the normalization of your data
structure -- more than one fact in a field is not good database design.

That said, if I had that set of data, I'd use a query, add that field, and
use something like the following in the criterion (untested):

Like *manager* and Not Like *nurse*

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Thanks, all for your replies.

KenSheridan via AccessMonster.com said:
You need to include the asterisk wildcard character. In query design view:

Like "*manager*" And Not Like "*nurse*"

in the criteria row of the column should do it. This does assume that
neither 'manager' nor 'nurse' will be substrings of other words within the
column. To find them only as complete 'words' the following function can be
used:

Public Function FindWord(varFindIn As Variant, varWord As Variant) As Boolean

Const PUNCLIST = " .,?!:;(){}[]"
Dim intPos As Integer

FindWord = False

If Not IsNull(varFindIn) And Not IsNull(varWord) Then
intPos = InStr(varFindIn, varWord)

' loop until no instances of sought substring found
Do While intPos > 0
' is it at start of string
If intPos = 1 Then
' is it whole string?
If Len(varFindIn) = Len(varWord) Then
FindWord = True
Exit Function
' is it followed by a space or punctuation mark?
ElseIf InStr(PUNCLIST, Mid(varFindIn, intPos + Len(varWord),
1)) > 0 Then
FindWord = True
Exit Function
End If
Else
' is it precedeed by a space or punctuation mark?
If InStr(PUNCLIST, Mid(varFindIn, intPos - 1, 1)) > 0 Then
' is it at end of string or followed by a space or
punctuation mark?
If InStr(PUNCLIST, Mid(varFindIn, intPos + Len(varWord),
1)) > 0 Then
FindWord = True
Exit Function
End If
End If
End If

' remove characters up to end of first instance
' of sought substring before looping
varFindIn = Mid(varFindIn, intPos + 1)
intPos = InStr(varFindIn, varWord)
Loop
End If

End Function

Add it to a standard module and call it in the query like so, in SQL view:

WHERE FindWord([YourFieldName],"manager")
AND NOT FindWord([YourFieldName],"nurse")

Or in design view put the following in the 'field' row of a blank column:

FindWord([YourFieldName],"manager")

Uncheck the 'show' checkbox, and in its first 'criteria' row put:

True

Put the following in the 'field' row of a another blank column:

FindWord([YourFieldName],"nurse")

Uncheck the 'show' checkbox, and in its first 'criteria' row put:

False

Ken Sheridan
Stafford, England
I have been successful in selecting records in a table that bear the word,
"manager" in one field.

I also need to exclude records in the same table that bear the word,
"nurse", in the same field.

Nothing like, <Like "manager" AND NOT Like "Nurse"> has worked, and clearly
I don't know the syntax for this selection criterion.

If this is clear, could somebody make a suggestion?

Thanks.

Don.
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...

And since all asked the same question, I was obviously not clear, even
though the question was probably answered.

Therefore, please allow me to be more specific:

The field in question contains a multiple of words, being the job title of
all employees. I want to seek all Managers and Supervisors and Officers and
Directors. My criteria line that is successful in picking these out is:

Like "*Manager*" Or Like "*Officer*" Or Like "*Chief*" Or Like "*Director*"

This works fine, but also catches one position that is not administrative,
Nurse Case Manager. So elected to somehow skip records containing the word,
"Nurse". It appears that your excellent answers concur in how to do this, but
this set of criteria do NOT return the results I am seeking:

Like "*Manager*" Or Like "*Officer*" Or Like "*Chief*" Or Like "*Director*"
And Not Like "*Nurse*"

So does anyone see an error here?

Thanks.

Don.
 
Hah!

Sometimes it takes just talking about a problem to get somewhere. The
criteria set that works is:

Not Like "*Nurse*" And Like "*Manager*" Or Like "*Officer*" Or Like
"*Chief*" Or Like "*Director*"

Interesting, that I have to get Access to eliminate the unwanted matter
before stating what I actually want included. Does this involve some
hierarchy of operatives?

Also, I note that once I reorganized the above, Access changed it so each
criterion appears on a separate line, and I note that they are all preceded
by a single "or:". Never saw THAT coming.

Don.

PlarfySoober said:
Thanks, all for your replies.

KenSheridan via AccessMonster.com said:
You need to include the asterisk wildcard character. In query design view:

Like "*manager*" And Not Like "*nurse*"

in the criteria row of the column should do it. This does assume that
neither 'manager' nor 'nurse' will be substrings of other words within the
column. To find them only as complete 'words' the following function can be
used:

Public Function FindWord(varFindIn As Variant, varWord As Variant) As Boolean

Const PUNCLIST = " .,?!:;(){}[]"
Dim intPos As Integer

FindWord = False

If Not IsNull(varFindIn) And Not IsNull(varWord) Then
intPos = InStr(varFindIn, varWord)

' loop until no instances of sought substring found
Do While intPos > 0
' is it at start of string
If intPos = 1 Then
' is it whole string?
If Len(varFindIn) = Len(varWord) Then
FindWord = True
Exit Function
' is it followed by a space or punctuation mark?
ElseIf InStr(PUNCLIST, Mid(varFindIn, intPos + Len(varWord),
1)) > 0 Then
FindWord = True
Exit Function
End If
Else
' is it precedeed by a space or punctuation mark?
If InStr(PUNCLIST, Mid(varFindIn, intPos - 1, 1)) > 0 Then
' is it at end of string or followed by a space or
punctuation mark?
If InStr(PUNCLIST, Mid(varFindIn, intPos + Len(varWord),
1)) > 0 Then
FindWord = True
Exit Function
End If
End If
End If

' remove characters up to end of first instance
' of sought substring before looping
varFindIn = Mid(varFindIn, intPos + 1)
intPos = InStr(varFindIn, varWord)
Loop
End If

End Function

Add it to a standard module and call it in the query like so, in SQL view:

WHERE FindWord([YourFieldName],"manager")
AND NOT FindWord([YourFieldName],"nurse")

Or in design view put the following in the 'field' row of a blank column:

FindWord([YourFieldName],"manager")

Uncheck the 'show' checkbox, and in its first 'criteria' row put:

True

Put the following in the 'field' row of a another blank column:

FindWord([YourFieldName],"nurse")

Uncheck the 'show' checkbox, and in its first 'criteria' row put:

False

Ken Sheridan
Stafford, England
I have been successful in selecting records in a table that bear the word,
"manager" in one field.

I also need to exclude records in the same table that bear the word,
"nurse", in the same field.

Nothing like, <Like "manager" AND NOT Like "Nurse"> has worked, and clearly
I don't know the syntax for this selection criterion.

If this is clear, could somebody make a suggestion?

Thanks.

Don.
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...

And since all asked the same question, I was obviously not clear, even
though the question was probably answered.

Therefore, please allow me to be more specific:

The field in question contains a multiple of words, being the job title of
all employees. I want to seek all Managers and Supervisors and Officers and
Directors. My criteria line that is successful in picking these out is:

Like "*Manager*" Or Like "*Officer*" Or Like "*Chief*" Or Like "*Director*"

This works fine, but also catches one position that is not administrative,
Nurse Case Manager. So elected to somehow skip records containing the word,
"Nurse". It appears that your excellent answers concur in how to do this, but
this set of criteria do NOT return the results I am seeking:

Like "*Manager*" Or Like "*Officer*" Or Like "*Chief*" Or Like "*Director*"
And Not Like "*Nurse*"

So does anyone see an error here?

Thanks.

Don.
Click to expand...
 
Ands are always evaluated before Ors.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


PlarfySoober said:
Hah!

Sometimes it takes just talking about a problem to get somewhere. The
criteria set that works is:

Not Like "*Nurse*" And Like "*Manager*" Or Like "*Officer*" Or Like
"*Chief*" Or Like "*Director*"

Interesting, that I have to get Access to eliminate the unwanted matter
before stating what I actually want included. Does this involve some
hierarchy of operatives?

Also, I note that once I reorganized the above, Access changed it so each
criterion appears on a separate line, and I note that they are all
preceded
by a single "or:". Never saw THAT coming.

Don.

PlarfySoober said:
Thanks, all for your replies.

KenSheridan via AccessMonster.com said:
You need to include the asterisk wildcard character. In query design
view:

Like "*manager*" And Not Like "*nurse*"

in the criteria row of the column should do it. This does assume that
neither 'manager' nor 'nurse' will be substrings of other words within
the
column. To find them only as complete 'words' the following function
can be
used:

Public Function FindWord(varFindIn As Variant, varWord As Variant) As
Boolean

Const PUNCLIST = " .,?!:;(){}[]"
Dim intPos As Integer

FindWord = False

If Not IsNull(varFindIn) And Not IsNull(varWord) Then
intPos = InStr(varFindIn, varWord)

' loop until no instances of sought substring found
Do While intPos > 0
' is it at start of string
If intPos = 1 Then
' is it whole string?
If Len(varFindIn) = Len(varWord) Then
FindWord = True
Exit Function
' is it followed by a space or punctuation mark?
ElseIf InStr(PUNCLIST, Mid(varFindIn, intPos +
Len(varWord),
1)) > 0 Then
FindWord = True
Exit Function
End If
Else
' is it precedeed by a space or punctuation mark?
If InStr(PUNCLIST, Mid(varFindIn, intPos - 1, 1)) > 0
Then
' is it at end of string or followed by a space or
punctuation mark?
If InStr(PUNCLIST, Mid(varFindIn, intPos +
Len(varWord),
1)) > 0 Then
FindWord = True
Exit Function
End If
End If
End If

' remove characters up to end of first instance
' of sought substring before looping
varFindIn = Mid(varFindIn, intPos + 1)
intPos = InStr(varFindIn, varWord)
Loop
End If

End Function

Add it to a standard module and call it in the query like so, in SQL
view:

WHERE FindWord([YourFieldName],"manager")
AND NOT FindWord([YourFieldName],"nurse")

Or in design view put the following in the 'field' row of a blank
column:

FindWord([YourFieldName],"manager")

Uncheck the 'show' checkbox, and in its first 'criteria' row put:

True

Put the following in the 'field' row of a another blank column:

FindWord([YourFieldName],"nurse")

Uncheck the 'show' checkbox, and in its first 'criteria' row put:

False

Ken Sheridan
Stafford, England

PlarfySoober wrote:
I have been successful in selecting records in a table that bear the
word,
"manager" in one field.

I also need to exclude records in the same table that bear the word,
"nurse", in the same field.

Nothing like, <Like "manager" AND NOT Like "Nurse"> has worked, and
clearly
I don't know the syntax for this selection criterion.

If this is clear, could somebody make a suggestion?

Thanks.

Don.

--
Message posted via AccessMonster.com


.
Click to expand...

And since all asked the same question, I was obviously not clear, even
though the question was probably answered.

Therefore, please allow me to be more specific:

The field in question contains a multiple of words, being the job title
of
all employees. I want to seek all Managers and Supervisors and Officers
and
Directors. My criteria line that is successful in picking these out is:

Like "*Manager*" Or Like "*Officer*" Or Like "*Chief*" Or Like
"*Director*"

This works fine, but also catches one position that is not
administrative,
Nurse Case Manager. So elected to somehow skip records containing the
word,
"Nurse". It appears that your excellent answers concur in how to do this,
but
this set of criteria do NOT return the results I am seeking:

Like "*Manager*" Or Like "*Officer*" Or Like "*Chief*" Or Like
"*Director*"
And Not Like "*Nurse*"

So does anyone see an error here?

Thanks.

Don.
Click to expand...
Click to expand...
 
Don't know if this would work, but possibly worth a try...

Have you looked into using the In() expression to list the ones you want
found?

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

PlarfySoober said:
Hah!

Sometimes it takes just talking about a problem to get somewhere. The
criteria set that works is:

Not Like "*Nurse*" And Like "*Manager*" Or Like "*Officer*" Or Like
"*Chief*" Or Like "*Director*"

Interesting, that I have to get Access to eliminate the unwanted matter
before stating what I actually want included. Does this involve some
hierarchy of operatives?

Also, I note that once I reorganized the above, Access changed it so each
criterion appears on a separate line, and I note that they are all
preceded
by a single "or:". Never saw THAT coming.

Don.

PlarfySoober said:
Thanks, all for your replies.

KenSheridan via AccessMonster.com said:
You need to include the asterisk wildcard character. In query design
view:

Like "*manager*" And Not Like "*nurse*"

in the criteria row of the column should do it. This does assume that
neither 'manager' nor 'nurse' will be substrings of other words within
the
column. To find them only as complete 'words' the following function
can be
used:

Public Function FindWord(varFindIn As Variant, varWord As Variant) As
Boolean

Const PUNCLIST = " .,?!:;(){}[]"
Dim intPos As Integer

FindWord = False

If Not IsNull(varFindIn) And Not IsNull(varWord) Then
intPos = InStr(varFindIn, varWord)

' loop until no instances of sought substring found
Do While intPos > 0
' is it at start of string
If intPos = 1 Then
' is it whole string?
If Len(varFindIn) = Len(varWord) Then
FindWord = True
Exit Function
' is it followed by a space or punctuation mark?
ElseIf InStr(PUNCLIST, Mid(varFindIn, intPos +
Len(varWord),
1)) > 0 Then
FindWord = True
Exit Function
End If
Else
' is it precedeed by a space or punctuation mark?
If InStr(PUNCLIST, Mid(varFindIn, intPos - 1, 1)) > 0
Then
' is it at end of string or followed by a space or
punctuation mark?
If InStr(PUNCLIST, Mid(varFindIn, intPos +
Len(varWord),
1)) > 0 Then
FindWord = True
Exit Function
End If
End If
End If

' remove characters up to end of first instance
' of sought substring before looping
varFindIn = Mid(varFindIn, intPos + 1)
intPos = InStr(varFindIn, varWord)
Loop
End If

End Function

Add it to a standard module and call it in the query like so, in SQL
view:

WHERE FindWord([YourFieldName],"manager")
AND NOT FindWord([YourFieldName],"nurse")

Or in design view put the following in the 'field' row of a blank
column:

FindWord([YourFieldName],"manager")

Uncheck the 'show' checkbox, and in its first 'criteria' row put:

True

Put the following in the 'field' row of a another blank column:

FindWord([YourFieldName],"nurse")

Uncheck the 'show' checkbox, and in its first 'criteria' row put:

False

Ken Sheridan
Stafford, England

PlarfySoober wrote:
I have been successful in selecting records in a table that bear the
word,
"manager" in one field.

I also need to exclude records in the same table that bear the word,
"nurse", in the same field.

Nothing like, <Like "manager" AND NOT Like "Nurse"> has worked, and
clearly
I don't know the syntax for this selection criterion.

If this is clear, could somebody make a suggestion?

Thanks.

Don.

--
Message posted via AccessMonster.com


.
Click to expand...

And since all asked the same question, I was obviously not clear, even
though the question was probably answered.

Therefore, please allow me to be more specific:

The field in question contains a multiple of words, being the job title
of
all employees. I want to seek all Managers and Supervisors and Officers
and
Directors. My criteria line that is successful in picking these out is:

Like "*Manager*" Or Like "*Officer*" Or Like "*Chief*" Or Like
"*Director*"

This works fine, but also catches one position that is not
administrative,
Nurse Case Manager. So elected to somehow skip records containing the
word,
"Nurse". It appears that your excellent answers concur in how to do this,
but
this set of criteria do NOT return the results I am seeking:

Like "*Manager*" Or Like "*Officer*" Or Like "*Chief*" Or Like
"*Director*"
And Not Like "*Nurse*"

So does anyone see an error here?

Thanks.

Don.
Click to expand...
Click to expand...
 
Back
Top