SWITCH statement

davea300

Registered User.
Local time
Today, 14:40
Joined
Mar 16, 2007
Messages
164
Using access 2003, I am trying to write a SWITCH statement for a report header. I have a field TRADE which changes value on each page. I have an unbound field NUMBER which needs to change depending on what is shown in TRADE. (The field NUMBER will take a value from another field)

I guessed at the statement below:

SWITCH ([TRADE]="1",[NUMBER]=[ELECJOBNUMBER], [TRADE]="2",[NUMBER]=[JOINJOBNUMBER], [TRADE]="3",[NUMBER]=[PAINTJOBNUMBER] etc etc

My syntax doesn't work when I place this code in the On Format event of the report. Should I be placing it in the NUMBER field itself.
I've previously used IIF statements sucessfully but this time I have too many potential values and it was getting messy.
Just need some help with the syntax and placing of this SWITCH statement.
 
What's the data type of Trade?

And you really should avoid names like Number. It's reserved for Access and could cause problems.
 
Data type is a lookup (TEXT) and NUMBER was just used as an example. I haven't used my actual field names :)
 
I meant it looks up values in a separate table but the data type is text.

I'm looking into a CASE statement to do what I want.
 

Users who are viewing this thread

Back
Top Bottom