IIF Statement in Query Criteria ... Stuck

[friedboudinball]

I have a form with a check box. A query is run that looks at that check box and decides what the criteria are based on that. So, if the check box is checked, it should pull in all data in the field that is a Y. If it is not checked, i want it to pull all data (Y's and N's and blanks).

here is my criteria:

IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y","*")

This does not seem to work. I have also tried:

IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y")
IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y",Like "*")
IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y","like "*"")

Please provide some assistance. Thank you in advance.

 

[KenHigg]

You have to embed the next if inside the true or false part of the subsequent if

 

[BlueIshDan]

".. WHERE [field] LIKE IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y","**")

 

[friedboudinball]

You have to embed the next if inside the true or false part of the subsequent if

Why would i need to do that? If the statement is true, i want it to say "Y" ... if it is not true, i want it to be a wild card (or the criteria doesn't exist). I can't seem to get the wild card to work.

 

[GinaWhipp]

Okay, I'm confused...

The asterisk means nothing in terms of criteria in this case. You can use it to *Show All*. You could try...

IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y","")

But in all honesty, I would use a Select Case statement to control which records I want to see.

 

[friedboudinball]

Okay, I'm confused...

The asterisk means nothing in terms of criteria in this case. You can use it to *Show All*. You could try...

IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y","")

But in all honesty, I would use a Select Case statement to control which records I want to see.

I have tried this to no avail either ... what is Select Case statement?

 

[GinaWhipp]

Something like...

Select Case "YourCheckBox"
     Case True
      Me.RecordSource = "YourFilteredQuery"
     Case Else
      Me.RecordSource = "YourUnfliteredQuery"
End Select

You would use an UNBOUND Check Box say in the Header of the Form for Users to toggle with.

 

[KenHigg]

Why would i need to do that? If the statement is true, i want it to say "Y" ... if it is not true, i want it to be a wild card (or the criteria doesn't exist). I can't seem to get the wild card to work.

You're correct - I misread the issue

So if the checkbox is selected the you want to filter for 'Y', else no filter?

 

[friedboudinball]

Yes. Thanks in advance

 

[GinaWhipp]

Have you tried the Select Case statement?

 

[vbaInet]

I hope I'm not interrupting

ISo, if the check box is checked, it should pull in all data in the field that is a Y. If it is not checked, i want it to pull all data (Y's and N's and blanks).

You can coalesce the Null records to 'N' in the query and put a condition on that field but this won't be optimised.

I would definitely go with Gina's solution of using a function but here's a query solution fyi:

WHERE [FieldName] = IIf([Forms]![frm_Query_Form]![CheckBox]=-1,'Y','N') OR [FieldName] LIKE IIf([Forms]![frm_Query_Form]![CheckBox]=-1,'Y','*')

 

[GinaWhipp]

vbaInet...

Interrupt? You??? Always happy to hear from you!

 

[vbaInet]

That's nice to know Gina

 

[friedboudinball]

for some reason this doesn't work ... the query runs fine when the select case isn't in there, but the criteria doesn't seem to want to work. any ideas?

This fails when check box is checked or unchecked:
(("WHERE [Applicable?]")=IIf([Forms]![frm_Query_Form]![CheckBox]=-1,"Y","N") Or ("WHERE [Applicable?]")=IIf([Forms]![frm_Query_Form]![CheckBox]=-1,"Y","*")));

This fails when check box is checked but works when unchecked:
(("WHERE [Applicable?]") Like IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y","**")));

 

[vbaInet]

What code doesn't work?

 

[friedboudinball]

see post 14

 

[vbaInet]

What you've written doesn't match what I gave.

All you have to do is copy exactly what I wrote and amend FieldName to the name of the field you're testing against. Make sure you leave the square brackets too.

Also it's bad practice to use names like Checkbox and names that contain spaces, question marks etc.

 

[friedboudinball]

This gives me a syntax error ...

... AND ( WHERE [Applicable?] = IIf([Forms]![frm_Query_Form]![CheckBox]=-1,'Y','N') OR [Applicable?] LIKE IIf([Forms]![frm_Query_Form]![CheckBox]=-1,'Y','*'));

 

[vbaInet]

Let me see the full SQL statement.

 

[GinaWhipp]

You know I think something is missing here, so let me see if I understanding this...

Are you putting that IIf() statement in a Control on the Form? If yes, how are you expecting it to Filter the Record Source? Is there some code behind the Form that looks at this Control?