IIF with Switch for better readability?

[Minkowski]

hello everyone!
i have multiple if and i would like to know if there is a more readable way to to this:

3 options: for field 1
1a,1b,1c
then another 3: for field 2
2a,2b,2c
and finally 3 more: for field 3
3a,3b,3c

if 1a & 2a & 3a true give me value 10
if 1a & 2b & 3a true give me value 15
if 1a & 2c & 3a true give me value20
if 1b & 2a & 3a true give me value 4
.....
all possible combinations
there must be a better way to to this than an almost endless IIf

thanks in advance

 

[alansidman]

I think that you may be better off using a Case Select in VBA.

Look at these two links on how to do this.

http://www.techonthenet.com/access/functions/advanced/case.php

http://www.databasedev.co.uk/select-case-statement.html

Alan

 

[Minkowski]

allright
i have table with all the fields i want to filter

where can i find how to run this?

lets say i have it ready...

Select Case [Region]
   Case "N"
      [RegionName] = "North"
   Case "S"
      [RegionName] = "South"
   Case "E"
      [RegionName] = "East"
   Case "W"
      [RegionName] = "West"
End Select

i never used vba in access only excel
how do i proceed?
and get the table filtered with the above cases?

 

[xenou]

Simplest might be (no one seems to take me up on this though):
Just right click in the region field and use Access' native filtering features to filter the dataset with no code and no comboboxes and no work (except learning how to use Access filters if you don't know how - Access has excellent filtering features that are available from its menus and right click options).

Also not to hard to do would be to put your comboboxes in a main form, and drop the data into a subform. Then you should get immediate changes based on the selection.

Otherwise, you'll have to use some code or a macro to apply a filter to the form based on the selection (perhaps in an after update event or via button click event). You can provide more options and features for your user this way (eg, option buttons and listboxes populated with choices and so on).

ξ