Specifying a column for a parameter query without IIf

[Sam92Legacy]

I am writing a parameter query where not only are the criteria for the query based on the value of fields in a form, but I also need to specify what column to use based on the value of a combo box in the form. For instance, if the value selected in the form is "Bob Total" I need to select the column [Others Summary].[Bob Total] in my query. Right now I am using an IIf statement to do the task, but I would rather find a more efficient solution that does not require updating the the column's expression when ever I add rows in the combo list (and doesn't end with 40 parentheses). Does anyone have any suggestions? Thanks.

 

[raskew]

Check-out the Switch() function in the help file.

Have a feeling you may be dealing with a non-normalized table, maybe from an Excel spreadsheet. If your table has such fields as bobtotal, samtotal, joetotal, sallytotal, etc., then you would be well advised to read-up on normalization.

 

[Sam92Legacy]

Thanks for the tip on Switch (). Works perfectly.

Actually, I think my table is (somewhat) normalized: it is a summary table used to specify total contributions to individuals. THe "Bob" of "Bob Total" refers to sum of contributions made to Bob by different individuals (related to my Main contact table through a foreign key).