"If...Then" Statement using multiple criteria

[Lauren B]

I'm attempting to write an "If...Then" statement and am encountering a
syntax error.

In the AfterUpdate property for the field "STATE," I am writing a code that
will automatically populate the field "DISTRICT." The districts are
predefined - for example, Distict 1 consists of CT, MA, ME, and NH. Thus, I
would like my code to populate the field "DISTRICT" with the text "District
1" for any entries in the states of CT, MA, ME, or NH. I've tried the
following code:

If STATE = "CT" Or "MA" Or "ME" Or "NH" Then
DISTRICT = "District 1"
End If

I am getting a syntax error on the "CT" Or "MA" Or "ME" Or "NH" portion of
the statement. How can I code the multiple criteria correctly?

Thank you in advance for any assistance.

LB

 

[Guest]

You should use:

IF (STATE = "CT") OR (STATE = "MA") OR (STATE = "ME") OR (STATE = "NH") THEN
.....
END IF

Chris

 

[Justin Hoffman]

I'm attempting to write an "If...Then" statement and am encountering a
syntax error.

In the AfterUpdate property for the field "STATE," I am writing a code
that will automatically populate the field "DISTRICT." The districts are
predefined - for example, Distict 1 consists of CT, MA, ME, and NH. Thus,
I would like my code to populate the field "DISTRICT" with the text
"District 1" for any entries in the states of CT, MA, ME, or NH. I've
tried the following code:

If STATE = "CT" Or "MA" Or "ME" Or "NH" Then
DISTRICT = "District 1"
End If

I am getting a syntax error on the "CT" Or "MA" Or "ME" Or "NH" portion of
the statement. How can I code the multiple criteria correctly?

Thank you in advance for any assistance.

LB

You could use the "Select Case" construct - shown here with two further
parts which are not necessary in your case, but demonstrate how you could
expand it. Of course, being a relational database with tables, it may be
better not to "hard code" these values, but have a district field in the
state table. That way your users can change the district without needing to
alter the vba code.
Anyway, if you do use code:

Select Case State

Case "CT", "MA", "ME", "NE"
DISTRICT = "District 1"

Case "ZZ"
DISTRICT = "District 99"

Case Else
DISTRICT = "District Unknown"

End Select

 

[John Vinson]

I'm attempting to write an "If...Then" statement and am encountering a
syntax error.

In the AfterUpdate property for the field "STATE," I am writing a code that
will automatically populate the field "DISTRICT." The districts are
predefined - for example, Distict 1 consists of CT, MA, ME, and NH. Thus, I
would like my code to populate the field "DISTRICT" with the text "District
1" for any entries in the states of CT, MA, ME, or NH. I've tried the
following code:

If STATE = "CT" Or "MA" Or "ME" Or "NH" Then
DISTRICT = "District 1"
End If

I am getting a syntax error on the "CT" Or "MA" Or "ME" Or "NH" portion of
the statement. How can I code the multiple criteria correctly?

Three suggestions:

1. The OR operator is *NOT* the English language conjunction; it's a
logical operator. It compares expressions, evaluates them as TRUE or
FALSE, and returns TRUE if either one of the expressions is TRUE.

"MA" is not a logical expression, it's a text string. (In my
recollection, "MA" is not a particularly logical state either!)

So the syntax would be

If STATE = "MA" OR STATE = "CT" OR STATE = "ME"...

so you're combining true/false expressions.

2. The IN() operator in SQL (not in VBA) lets you do this more
efficiently:

State IN("CT", "MA", "ME", "NH")

as a query criterion will return records just from those states.

3. You might want to consider a table-driven solution: a two field
table with fields STATE and DISTRICT. This could be simply Joined to
your table to display the district, with no code required at all.

John W. Vinson[MVP]