Comparison issue

[Guest]

I don't know why, for example, "Biology 127" is different with "Biology*".
When it evaluates the IF statement below, the statement inside the IF clause
is executed while I expect the Else statement should be executed.

If nz(Me!CLASS,"") <> "Biology*" Then
'Do this if True (i.e., if Null or not like Biology*)
Else
'Do this if False (i.e., like Biology*)
End If

 

[Douglas J. Steele]

Like (or Not Like) is only for SQL statements. Try:

If Left(nz(Me!CLASS,""), 7) <> "Biology" Then

 

[Stuart McCall]

Like (or Not Like) is only for SQL statements.

PMFJI. Not strictly true. "Like" can be used with strings, but not "Not
Like" (Expression expected). Try pasting this into the immediate window and
running it:

n = "Doug Steele, Microsoft Access MVP": ? n like "*Steele*"

or:

n = "Doug Steele, Microsoft Access MVP": ? n like "*Steele??Mic*"

 

[Stuart McCall]

I don't know why, for example, "Biology 127" is different with "Biology*".
When it evaluates the IF statement below, the statement inside the IF
clause
is executed while I expect the Else statement should be executed.

If nz(Me!CLASS,"") <> "Biology*" Then
'Do this if True (i.e., if Null or not like Biology*)
Else
'Do this if False (i.e., like Biology*)
End If
------
Can I use <>? It would pops up an error if I use"Not Like". I use SQL
Server as the backend. Can you please help me to solve the problem? Thank
you
in advance for your help.

You can use the "Like" operator with strings, but "Not Like" causes an
error, as you have found. This simply means you must turn your comparison
around:

If Nz(Me!CLASS, "") Like "Biology*" Then
'Do this if False (i.e., like Biology*)
Else
'Do this if True (i.e., if Null or not like Biology*)
End If

 

[Guest]

Stuart:
Thanks for your help. Your code works! But I would like to know that can I
use the "<>" comparison? It does not work to me.

 

[Douglas J. Steele]

You cannot use <> with a wild card.

If nz(Me!CLASS,"") <> "Biology*" Then

will always be true unless the contents of Me!Class is the literal string
Biology* (complete with asterisk)

 

[Stuart McCall]

Stuart:
Thanks for your help. Your code works! But I would like to know that can I
use the "<>" comparison? It does not work to me.

Doug has already answered you, so I'm including this simply to reinforce his
point. No you cannot use <> (or > or < or = or >= or <=) operators with
wildcards in order to detect partial matches. The ONLY operator with this
capability is the Like operator.