Switch Function Limit Prob (7 Expressions?)

[JulieCaliGirl]

Is there a trick/tweak around the 7 expression limit in Access 97 using the Switch function? I've been asked to create some reports in a db that is not "mine", If possible I would like to create reports w/o creating/altering any queries/tables in this db. Since it's used company wide, I'd like to avoid cluttering it up w/additional table/queries just to meet my depts needs. I need to convert about 10 values (similarly repeated in approx. 5 fields) in the report...works great for the first 7.

Any suggesttions are welcome,
Julie

 

[dhookom]

Can you provide a sample of how you are using the switch function? Do you know that you can create your own function?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JulieCaliGirl]

Thanks for responding dhookom. I ended up just creating a query using/joining some tables that have both the id#'s & text description. I guess I could have also written an "if/then/else". But, for future reference I like to know what my report options are using switch or ?. The following switch function worked great, but I didn't attempt to go beyond 7:

=Switch([SubProc_ID]="6","Calculations/Analysis",[SubProc_ID]="9"," Document Preparation & Processing",[SubProc_ID]="13","Configuration Management",[SubProc_ID]="18","Licensing Bases Management",[SubProc_ID]="24","CRDR Evaluation",[SubProc_ID]="40","Performance Monitoring",[SubProc_ID]="42","Planning & Scheduling",[SubProc_ID]="43","Contract Preparing/Monitoring",[SubProc_ID]="49","Other-Explain",[SubProc_ID]="99","Impact / X-Org Review&quot

Users simply need to know what processes (SubProc)are getting the most action. I will also sum them up etc., but they want to see each process + the corresponding data (which also have ID's instead of text like in the switch above).

While those that manage the db know what the SubProc_ID is, I didn't want the USERS to have to literally translate the ID to the corresponding text. (We use ID's instead of text because the data is used for trending purposes in an external db.)

Sorry so long...just thought I might be missing out on something.
Julie

 

[dhookom]

Since you had the lookup table for the ID and description, I would have never considered using Switch(). SQL is much more efficient and flexible. If you change the text description, with Switch, you would have to change the expression (yuk!).


Duane
MS Access MVP
Find out how to get great answers faq219-2884.