Use of "IN" statement - easy question

B

Biggles

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.
 
D

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
 
R

Rick Brandt

Biggles said:
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.
Click to expand...

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.
 
R

Rick Brandt

Douglas J. Steele said:
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
Click to expand...

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

Marshall Barton

Biggles said:
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
Click to expand...


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
 
B

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.
Click to expand...

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.
 
B

Biggles

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
 
Top