Iif Statement problem

  • Thread starter Thread starter Jacob Frankham
  • Start date Start date
J

Jacob Frankham

Hi

I have (yet another) question regarding my [risk] field.

I have a query, and it has [risk] as one of its fields.

I have an Iif statement in the Criteria section of my query which basically
checks to see which CheckBoxes are selected on my form in order to display
the appropriate risks in the query.

The combo boxes are: High, Medium, Low, Advisory

HOWEVER, I have just been informed that risks may now be classified as
High/Medium or Medium/Low !!!

I do not want to add these to my form as extra checkboxes (too messy), but
would like to add something to my query such that if High AND Medium are
selected, then display High, Medium and High/Medium in the query

Likewise for Medium/Low, If Medium and Low are selected on my form (the
checkboxes) then I wish the query to display Medium, Low and Medium/Low

I have tried THIS:
Iif([forms]![frm_Menu]![cbx_High] And [forms]![frm_Menu]![cbx_Medium],
In("High","Medium","Medium/Low"),"")

which I thought was ingenious at the time - until I ran the query and
realised that it didn't work !

Help, as always, would be welcomed !

Thanks

Jake
 
Jake,

IMHO it may be messy to add 2 more checkboxes to your criteria
selection form, but this is not half as messy as trying to work
combined criteria into your query! :-)

IMHO (again) it is almost never advisable to use an IIf() function in
a query criteria.

Here's a couple of methods I would consider...

1. Messy or not, add the extra checkboxes to your form. If I
understand you correctly, this would now make 6 all together. Then,
in the design view of the query, put criteria in 6 rows of the grid,
like this...
"High" And [forms]![frm_Menu]![cbx_High]<>0
"High/Medium" And [forms]![frm_Menu]![cbx_HM]<>0
etc

2. Make a little Risks table, 6 records, 2 fields, being RiskFactor
(Text) and Include (Yes/No). Enter your 6 risk categories in this
table. One advantage of this method is that factors can be added or
deleted later if necessary as needs/policies change, without having to
mess again with your form and query. Scrap the existing checkboxes on
the form, and replace them with a simple continuous view subform,
based on your new little Risks table, to show the 6 categories. Still
got 6 checkboxes, right? Now, in your query, add the new table and
join it to the existing table on the Risk/RiskFactor fields, put the
Include field into the query fields, and the only criteria you need is
to put <>0 in the criteria of the Include field. A variation on the
theme, is to just base your query on the existing table as before, and
in the criteria of the Risk field, put...
In(SELECT RiskFactor FROM Risks WHERE Include<>0)

- Steve Schapel, Microsoft Access MVP
 
WHERE (((Risk)="High") AND ([forms]![frm_Menu]![cbx_High])) OR
(((Risk)="High/Medium") AND ([forms]![frm_Menu]![cbx_High]) AND
([forms]![frm_Menu]![cbx_Medium])) OR (((Risk)="Medium") AND
([forms]![frm_Menu]![cbx_Medium])) OR (((Risk)="Medium/Low") AND
([forms]![frm_Menu]![cbx_Medium]) AND ([forms]![frm_Menu]![cbx_Low])) OR
(((Risk)="Low") AND ([forms]![frm_Menu]![cbx_Low])) OR (((Risk)="Advisory")
AND ([forms]![frm_Menu]![cbx_Advisory]));



Peter
 
Back
Top