IIF Statement in Query Criteria ... Stuck

[friedboudinball]

Data is entered into a form.

Based on what data are entered in the form, the query pulls the information. I have a check box on the form, as well. If the check box is checked, i want the query to have a criteria that says "Y." If it is not checked, i want it to pull all data based on other form criteria.

 

[GinaWhipp]

Hmm, but you are not setting the WHERE clause for the Record Source anywhere? It won't change based on that Check Box without also *telling* the Record Source. So, is that waht you are doing? If so, please post the Record Source and the After_Update event of the CHeck Box... that will help to know why it's not working.

 

[friedboudinball]

Hmm, but you are not setting the WHERE clause for the Record Source anywhere? It won't change based on that Check Box without also *telling* the Record Source. So, is that waht you are doing? If so, please post the Record Source and the After_Update event of the CHeck Box... that will help to know why it's not working.

SELECT tbl_Master_Table_Profile_Data.[Entity Name], tbl_Master_Table_Profile_Data.[Applicable?]
FROM tbl_Master_Table_Profile_Data
WHERE (((tbl_Master_Table_Profile_Data.[Entity Name]) Like "*" & [Forms]![frm_Query_Form]![EntityCombo] & "*") AND ((tbl_Master_Table_Profile_Data.[Applicable?])=IIf([Forms]![frm_Query_Form]![CheckBox]=-1,"Y","**")));

In this case, when the check box is checked, i want it to return all "Y" data points. This works. It does not work when i uncheck the box. I want all records when the check box is not checked.

 

[GinaWhipp]

And removing the asterisks does not do that?

 

[vbaInet]

It looks like I got my logic wrong. Here's one that should work:

SELECT [Entity Name], [Applicable?]
FROM tbl_Master_Table_Profile_Data
WHERE [Entity Name] = IIF(Nz([Forms]![frm_Query_Form]![EntityCombo],0) = -1, 'Y', 'N') 
           OR 
           (IIf([Entity Name] Is Null Or [Entity Name] = 'N', 0, -1) = Nz([Forms]![frm_Query_Form]![EntityCombo], 0));

 

[friedboudinball]

No dice ... expression typed in correctly or is too complex

 

[vbaInet]

Let's see what you've got.

 

[GinaWhipp]

If this is a query then try If() not IIf(), I adjusted vbInet's line to reflect the change.

SELECT [Entity Name], [Applicable?]FROM tbl_Master_Table_Profile_Data WHERE [Entity Name] = If(Nz([Forms]![frm_Query_Form]![EntityCombo],0) = -1, 'Y', 'N') OR (If([Entity Name] Is Null Or [Entity Name] = 'N', 0, -1) = Nz([Forms]![frm_Query_Form]![EntityCombo], 0));

 

[vbaInet]

Only IIF() works in a query. Have you had your coffee this morning Gina?

It's either friedboudinball hasn't typed it in correctly or his data type don't match.

 

[GinaWhipp]

vbaInet...Nope, I had just gotten up when I typed that... so sorry! :banghead:

 

[vbaInet]

I've had 2 cups already

 

[GinaWhipp]

I am now on my second cup, so *almost* awake!