VBA Code instead of Expression

[Telesphore]

In a Report TextBox, instead of this expression that Access 2003 declares as
"too complex":

=SWITCH(
[State]="AL";"ALABAMA";
[State]="AK";"ALASKA";
[State]="AZ";"ARIZONA";
[State]="AR";"ARKANSAS";
[State]="CA";"CALIFORNIA";
ETC.)

We would like to use VBA Code, for example:

Private Sub Report_Load()
txtState.Value = IIf(.
..
End Sub


Any help will be appreciated.

Thank you

 

[Allen Browne]

Hey, Access is a relational database. Can we encourage you to use it that
way?

Create a table with 2 fields:
StateID Text primary key
StateName Text the full name of the state.
Then create a query that combines you existing table and this table. The
query will output the StateName, and you don't need any code at all.

If you really want to use VBA code, see help on Select Case. But that's a
bit like using a tennis racquet to bang in a tent peg.

 

[Klatuu]

Allen is correct, but I would like to point out two problems with your
Switch function. First, the correct seperator is a comma, not a semicolon,
secondly there may be a limit to the number of conditions you can include.
I can't remember if this is true of Switch, but it is for some other
functions.

But, in a relational database, data belongs in tables. Use a translation
table as Allen suggests.

 

[Rick Brandt]

Allen is correct, but I would like to point out two problems with your
Switch function. First, the correct seperator is a comma, not a
semicolon [snip]

In international versions of Access the delimiter for most functions is the
semi-colon rather than the comma.

 

[Klatuu]

Thanks, Rick. I was not aware of that.

Allen is correct, but I would like to point out two problems with your
Switch function. First, the correct seperator is a comma, not a
semicolon [snip]

In international versions of Access the delimiter for most functions is
the semi-colon rather than the comma.