Thanks, all for your replies.
"KenSheridan via AccessMonster.com" wrote:
> 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
> http://www.accessmonster.com/Uwe/For...arted/201001/1
>
> .
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.
Similar Threads
