IIF statment with OR

[tMitch]

Hi

I am using a list box in a form (frmPlantListDialog) as the criteria for a query. The list box has 4 rows. I want the criteria to say:

IIf([Forms]![frmPlantListDialog]![cboPType]=1,1 Or 2,IIf([Forms]![frmPlantListDialog]![cboPType]=2,3 Or 4,IIf([Forms]![frmPlantListDialog]![cboPType]=3,5,[PLANT_TYPE_ID])))

However, the true part of the statement doesn't work with the OR. It works fine otherwise, so the problem is with the OR. How do I write the code for this? I've tried various methods, but none have worked.

Thanks.

 

[Mile-O]

Instead of 1 OR 2, try:

Between 1 And 2

 

[tMitch]

That doesn't work either. Both work as criteria alone, but not as part of my IIf statement.

Any other suggestions?

Thanks.

 

[Jon K]

In the query grid, a criterion that starts with an IIF() function has to be put in the Field: cell (not the Criteria: cell). And instead of stating 1 or 2, you need to state the field name as well, e.g.

IIf([Forms]![frmPlantListDialog]![cboPType]=1, [PLANT_TYPE_ID]=1 Or [PLANT_TYPE_ID]=2, IIf(..... etc.


And put <>False in the Criteria: cell.

 

[neileg]

Am I going mad?

An IIf statement looks like (Test that returns true or false, value if true, value if false)

How on earth can an OR have a place in either of the value sections?

Please explain...

 

[tMitch]

Jon,

Thank you, that worked.

Neil, I'm sorry, but it seems like you might just be going mad....I'm not sure what you don't understand. Based on what is selected in my combo box, I want to select 1 or 2 plant_type_ids: ie, if 1 (Trees) is chosen in the form combo box, then I want plant_type_id 1 (deciduous) and 2 (evergreen) to be selected in the query.

Hope that helps.

 

[tMitch]

Hi Pat -

Thanks for your advice. I wonder if you can explain this more simply for me or point me toward an example (I'm not very experienced with Access or writing code). I'm not sure which part of my approach is wrong - trying to use the cboBox to select specific records in my query, or how I selected those records (Jon K's suggestion). And do you mean by

"SomeField = 'A' Or 'B'" will NOT work, you need "SomeField = 'A' Or SomeField = 'B""

that instead of my original statement of "1 OR 2", it should be "[Plant_Type_ID] = 1 OR [Plant_Type_ID] = 2"?

His suggestion worked, so do you mean that this is a bad approach because it could be problematic further down the line?

Thanks!

 

[neileg]

tMitch

No I'm not going mad! The quotes round the "1 OR 2" changes the sense of the statement entirely. Still, wrong, but now I understand.

I might have been having a slow day, but I'm not mad, yet.