Switch

[DennisORA]

I am attempting to use Switch in an Access query statement (using query builder). My statement looks like :
FunctionName: Switch([Table!column] in ("1st"), "First", [Table!column] in ("2ndTest"), "Second"). (there are considerably more conditions) After copying and pasting the statement into my query, it is automatically changed to
FunctionName: Switch([Table!column] in (["1st"]), "[First"], [Table!column] in (["2ndTest"]), ["Second"]).

When I run the query, I get prompted for values for "1st", "First", "2ndTest", and "Second". My other switch statements work fine. Can anyone enlighten me as to why this might be happening?

I tried using the Iif statement instead and got the same results. When I limited my switch to one test condition and a test for true it ran without a hitch. As soon as I added another test, I started getting the prompts again.

Thanks,
Dennis __

 

[Remou]

That would normally be:

Switch([Table!column] ="1st", "First", [Table!column] = "2ndTest", "Second")

However, it would be much better to use a table with the various values and substitute values that could be joined to Table!Column.

 

[dhookom]

I agree with Remou regarding the lookup table. In addition the syntax [Table!column] is generally bracketed like

![column].

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I third that. Bag the switch.
1. Slow
2. As you can see hard to write
3. Extremely inflexible
4. Does not short circuit

If you have a lot of records the difference would be a query that runs instantaneously or a query that takes many seconds or minutes.

Build a table or at least a custom function with a select case.