IIF AND problem in query 1

[DrDance]

Have a real problem with checkboxes in a query.

I have all the query fine for when all or one or even some of the checkboxes are ticked.

The problem occurs if no checkboxes are ticked, for some reason it won't show all records.

EXAMPLE: "TRYING TO SHOW ALL RECORDS IF ALL BOXES ARE NOT CHECKED"
IIf([Forms]![SEARCH]![Check428]="0" And [Forms]![SEARCH]![Check430]="0" And [Forms]![SEARCH]![Check432]="0" And [Forms]![SEARCH]![Check434]="0" And [Forms]![SEARCH]![Check436]="0","*")

"AND THIS IS WHAT I HAVE TO SHOW IF ONE SOME OR ALL CHECKBOXES HAVE A TICK"
IIf([Forms]![SEARCH]![Check428]="-1","c")
IIf([Forms]![SEARCH]![Check432]="-1","m")
IIf([Forms]![SEARCH]![Check430]="-1","f")
IIf([Forms]![SEARCH]![Check436]="-1","g")

I'm not using VBA or SQL as I don't know how to incorporate it, I'd rather just build a parameter expression if anyone can help - thanks.
IIf([Forms]![SEARCH]![Check434]="-1","h")

 

[Golom]

The format of IIF is
[tt]
IIf(expr, truepart, falsepart)
[/tt]
All your IIF statements are missing the "falsepart" value.

You are using SQL ... you're just using the query designer to build it. Switch to SQL View in the query designer and copy and paste the code here so that we can see what you are trying to do.

 

[DrDance]

O.K. I found the SQL part, but it seems a little mixed up anyway...

=IIf([Forms]![SEARCH]![Check428]="0" And [Forms]![SEARCH]![Check430]="0" And [Forms]![SEARCH]![Check432]="0" And [Forms]![SEARCH]![Check434]="0" And [Forms]![SEARCH]![Check436]="0","*")) AND ((tblFIELD.TRAINER) Like IIf([forms]![SEARCH]![TRAINER]="","*",[forms]![SEARCH]![TRAINER])) AND (("Or") Like IIf([forms]![SEARCH]![Check430]="-1","f","*"))) OR (((tblFIELD.SEX)=IIf([Forms]![SEARCH]![Check428]="-1","c"))) OR (((tblFIELD.SEX)=IIf([Forms]![SEARCH]![Check432]="-1","m"))) OR (((tblFIELD.SEX)=IIf([Forms]![SEARCH]![Check430]="-1","f"))) OR (((tblFIELD.SEX)=IIf([Forms]![SEARCH]![Check436]="-1","g"))) OR (((tblFIELD.SEX)=IIf([Forms]![SEARCH]![Check434]="-1","h")))

the tablefield.trainer bit shouldn't be in with it.

I see what you're saying about lacking the three parts of the statement not being there, I need to incorporate it all in the one expression but am at a loss.

 

[dhookom]

The first thing I would do is make this more maintainable by renaming your check boxes. How do you understand what each check box means? Whenever you start building large expressions like this, you might want to consider using the Switch() function or possible creating a user-defined function.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Golom]

[tt]
WHERE
IIf( [Forms]![SEARCH]![Check428]= 0
And [Forms]![SEARCH]![Check430]= 0
And [Forms]![SEARCH]![Check432]= 0
And [Forms]![SEARCH]![Check434]= 0
And [Forms]![SEARCH]![Check436]= 0,

" tblFIELD.SEX LIKE '*'",

" tblFIELD.SEX=" & IIf ([forms]![SEARCH]![Check430]= -1, "'f'", "'z'") & " OR "
" tblFIELD.SEX=" & IIf ([Forms]![SEARCH]![Check428]= -1, "'c'", "'z'") & " OR "
" tblFIELD.SEX=" & IIf ([Forms]![SEARCH]![Check432]= -1, "'m'", "'z'") & " OR "
" tblFIELD.SEX=" & IIf ([Forms]![SEARCH]![Check436]= -1, "'g'", "'z'") & " OR "
" tblFIELD.SEX=" & IIf ([Forms]![SEARCH]![Check434]= -1, "'h'", "'z'")
[/tt]
I have included 'z' in each IIF to satisfy the specification of the IIF and to ensure that the resulting statement is properly formatted. Presumably "z" never actually occurs as a value of the "tblField.SEX" field.

You will want to plug this into the WHERE clause in SQL view ... not into the query designer.

 

[DrDance]

Golom,

Thankyou so much for your hekp and explanation, this is exactly what I was looking for.

I have a problem with SQL though, not sure where to put this.

When I put it into the SQL view, it said "syntax error (missing operator) in query expression..."

Could you tell me what I should do please?

 

[DrDance]

Golom,

You certainly pointed me in the right direction - thanks very much.

Although I couldn't get your code to work, here is the adapted solution which may not be perfect, but seems to work very well:

WHERE (((tblFIELD.SEX) Like IIf([Forms]![SEARCH]![Check428]="0" And [Forms]![SEARCH]![Check430]="0" And [Forms]![SEARCH]![Check436]="0" And [Forms]![SEARCH]![Check434]="0" And [Forms]![SEARCH]![Check432]="0","*",IIf([Forms]![SEARCH]![Check428]="-1","c",IIf([Forms]![SEARCH]![Check430]="-1","f",IIf([Forms]![SEARCH]![Check436]="-1","g",IIf([Forms]![SEARCH]![Check434]="-1","h",IIf([Forms]![SEARCH]![Check432]="-1","m")))))) Or (tblFIELD.SEX)=IIf([Forms]![SEARCH]![Check432]="-1","m") Or (tblFIELD.SEX)=IIf([Forms]![SEARCH]![Check434]="-1","h") Or (tblFIELD.SEX)=IIf([Forms]![SEARCH]![Check436]="-1","g") Or (tblFIELD.SEX)=IIf([Forms]![SEARCH]![Check430]="-1","f") Or (tblFIELD.SEX)=IIf([Forms]![SEARCH]![Check428]="-1","c")))

I then added a further Group By SEX and SHOW column in design view.

Thanks Golom.