VBA Code instead of Expression   

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

date: Wed, 8 Oct 2008 09:09:29 -0400   author:   Telesphore

Re: VBA Code instead of Expression   

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.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Telesphore"  wrote in message
news:C37B749A-13D6-4902-A202-6E3BF97F3C68@microsoft.com...
> 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

date: Wed, 8 Oct 2008 22:04:21 +0800   author:   Allen Browne lid

Re: VBA Code instead of Expression   

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.

"Telesphore"  wrote in message 
news:C37B749A-13D6-4902-A202-6E3BF97F3C68@microsoft.com...
> 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
>

date: Wed, 8 Oct 2008 09:14:09 -0500   author:   Klatuu

Re: VBA Code instead of Expression   

Klatuu wrote:
> 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.

-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com

date: Wed, 8 Oct 2008 13:42:11 -0500   author:   Rick Brandt

Re: VBA Code instead of Expression   

Thanks, Rick.  I was not aware of that.

"Rick Brandt"  wrote in message 
news:7a7Hk.141$%11.31@flpi144.ffdc.sbc.com...
> Klatuu wrote:
>> 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.
>
> -- 
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt   at   Hunter   dot   com
>
>
>

date: Thu, 9 Oct 2008 09:15:15 -0500   author:   Klatuu