Use of "IN" statement - easy question

[Guest]

How should this be written using an IN statement.

If [report rating] = "Unsatisfactory" Or [report rating] = "Needs
Improvement" Or [report rating] = "Inadequate" Or [report rating] =
"Adequate, except For" Then
MsgBox "This rating indicates there should be unsatisfactory phases.
Be sure to complete the boxes below"
End If

I am apparently so braindead I couldn't get it to work.

 

[Douglas J. Steele]

As far as I'm aware, IN can only be used in SQL, not in VBA.

If it were possible, it would likely be something like:

If [report rating] In ("Unsatisfactory", "Needs Improvement",
"Inadequate", "Adequate, except For") Then

 

[Rick Brandt]

How should this be written using an IN statement.

If [report rating] = "Unsatisfactory" Or [report rating] = "Needs
Improvement" Or [report rating] = "Inadequate" Or [report rating] =
"Adequate, except For" Then
MsgBox "This rating indicates there should be unsatisfactory phases.
Be sure to complete the boxes below"
End If

I am apparently so braindead I couldn't get it to work.

If [report rating] In("Unsatisfactory"
,"Needs Improvement"
, "Inadequate"
, "Adequate, except For") Then...

I used line breaks for clarity in this post. The actual code would all be one
line.

 

[Rick Brandt]

As far as I'm aware, IN can only be used in SQL, not in VBA.

If it were possible, it would likely be something like:

If [report rating] In ("Unsatisfactory", "Needs Improvement", "Inadequate",
"Adequate, except For") Then

Doug is correct. In() does not work in VBA if that is how you are trying to use
it.

 

[Marshall Barton]

How should this be written using an IN statement.

If [report rating] = "Unsatisfactory" Or [report rating] = "Needs
Improvement" Or [report rating] = "Inadequate" Or [report rating] =
"Adequate, except For" Then
MsgBox "This rating indicates there should be unsatisfactory phases.
Be sure to complete the boxes below"
End If

Actually, in a roundabout way, it can be done:

If Eval("""" & [report rating] _
& """ In(""Unsatisfactory"", ""Needs Improvement""," _
& """Inadequate"", ""Adequate, except For"")") Then
MagBox ...

BUT why bother with such an obscure statement, when you can
use:

Select Case [report rating]
Case "Unsatisfactory", "Needs Improvement", _
"Inadequate", "Adequate, except For"
MagBox ...
End Select

 

[Bob Hairgrove]

How should this be written using an IN statement.

If [report rating] = "Unsatisfactory" Or [report rating] = "Needs
Improvement" Or [report rating] = "Inadequate" Or [report rating] =
"Adequate, except For" Then
MsgBox "This rating indicates there should be unsatisfactory phases.
Be sure to complete the boxes below"
End If

I am apparently so braindead I couldn't get it to work.

The question is whether you need to use an "IN" statement at all. An
"IN" statement is used only in SQL. But the above should work just
fine in VBA code. What is it that necessitates an "IN" statement?

For example, if you have all the different ratings in a related table
with a numeric primary key (AutoNumber or something similar), then you
could build an SQL statement or a DCount() function which could use an
appropriate "IN" clause together with a subquery returning the primary
key values you need.

 

[Guest]

Well everyone, thanks for your replies. I guess it is good to know that I
couldn't do it because I can't type or something.

Marshall mentioned using the select case, I think that is where I will go,
although it seemed like overkill for what I thought I should be able to do.

Thanks All