Use of SWITCH in criteria field

[mredmond]

Can a SWITCH command return two or more values from a single comparison?

Ex: Switch([Field]=Val1, 1, [Field]=Val2, 2 or 3)

If I use QBE, I can put 1 or (2 OR 3) on the CriteriaLine depending on the value of Field and it will work fine. But how do I implement this using a Switch command.

I am trying to do this so that I can get criteria from a form and pass it to a report's datasource query.

 

[pdx_man]

Boy, this can really give you some pretty crazy results if you really sit down and think about it and how the SWITCH functions work.

Why not just have each piece of the SWITCH function as it's own separate line in the criteria rows?

So, for the criteria for Field1 would be Val1 and the criteria for your field where you wanted to have the SWITCH function would be 1. Then the next row in the QBE would be Val2 with a corresponding 2 Or 3.

You can do it as it's own calculated field as:
Eval: Switch([field1]=Val1,[field2]=1,[field1]=Val2,[field2] In (2,3))

Then have the criteria be -1, or True.

 

[mredmond]

Thanks for the comments.

The details of my file are like this: I work for Internal Audit and we keep a database of all our projects. Each project has a status, such as
0=No Work Performed yet,
1=Complete with Report Issued,
2=Complete with No Report,
3=Closed, No Work performed, etc.
4=In Progress

If the boss wants to see a report of all our complete projects, he needs to see statuses 1,2 & 3.

So I was building a criteria gathering form in which he could check COMPLETE and the form would return a value to get all three.

The way I actually solved this was to create a new field on the Query simply named Status with a value of True. Then in the criteria field, I used the SWITCH with all the necessary ANDs and ORs and a return value of TRUE to get the correct selection. To complicate matters, I had to do the same thing with the PRIORITY field. There could be any combinaton of STATUS and PRIORITY. So it's a big SELECT statement (too big for QBE. I had to use the SQL view), BUT IT WORKS!!!

Thanks again and wish me luck.