Expression Builder IIF problem.

[lawsonium]

Hi,

I am trying to write an expression in my query which, given a condition, it uses the following criteria:
"Carmarthenshire" Or "Ceredigion" Or "Neath Port Talbot" Or "Pembrokeshire" Or "Swansea"

So far I have come up with this:
IIf([Forms]![Main Menu]![cboRegion]="West Wales","Carmarthenshire" Or "Ceredigion" Or "Neath Port Talbot" Or "Pembrokeshire" Or "Swansea")

However the Query builder complains the expression is incorrect or too complex etc. etc...

If I simplify it to this:
IIf([Forms]![Main Menu]![cboRegion]="West Wales","Carmarthenshire")
it works fine but unfortunately this isn't what I need.

Any help much appreciated,

Matt.

 

[lawsonium]

Ok, I've been fiddling and I've come up with this:

IIf([Forms]![Main Menu]![cboRegion]="West Wales","Carmarthenshire") OR
IIf([Forms]![Main Menu]![cboRegion]="West Wales","Ceredigion") OR
IIf([Forms]![Main Menu]![cboRegion]="West Wales","Neath Port Talbot") OR
IIf([Forms]![Main Menu]![cboRegion]="West Wales","Pembrokeshire") OR
IIf([Forms]![Main Menu]![cboRegion]="West Wales","Swansea")

It seems to work but I just need some reassurance I have written the correct expression for this problem.

Any suggestions most welcome.

Thanks,

Matt.

 

[KeithG]

I don't understand each IIf statement test for the same value. So if cboRegion=West Wales it will always return Carmarthenshire

 

[lawsonium]

It seems to work. The output of the query includes all 5 of the regions. The OR's seem to work but I agree it doesn't look right.

What would the best syntax be?

Matt

 

[KeithG]

Are you using this as criteria in a query?

 

[neileg]

If you want to test for value in a list, use the IN operator.

IIf([Forms]![Main Menu]![cboRegion] IN ("Carmarthenshire", "Ceredigion","Neath Port Talbot", "Pembrokeshire", "Swansea"), Value if true, Value if False)

 

[lawsonium]

No, what I'm trying to do is something like this:

If comboBox = "West Wales" then set query criteria to "Carmarthenshire" OR
"Ceredigion" OR "Neath Port Talbot" OR "Pembrokeshire" OR "Swansea"

Thanks,

Matt.

 

[KeithG]

What if the combobox is not equal to Whales?

 

[lawsonium]

It will be equal to either West, North or South and I have a list of counties for each example.

 

[KeithG]

So if combo is equal to North Whales your want to apply different criteria correct. And so forth for East and West?

 

[lawsonium]

Yeak, that's right.

I can do it if the criteria is only a single county but I want a list of counties with a structure like this "county1" OR "county2" OR "county3" etc.

Matt.

 

[neileg]

Shifting Goalposts Alert!

You need to change your design. You need a table that holds the counties and the area (N,S,E,W). Join this table in your query, joining on the county field. Then apply the area from the combo box as a criterion to the area in the table.