IIF not returning data - if "false", should return all results

[MaineWebDad]

Greetings,
I've been banging my head on this for days, and what really makes this difficult is the number of websites which say it should work just fine!

I'm working with Access 2003 and trying to make use of the IIf function. I have given my fields generic names in the examples below.

I have a number of items I want to search upon in my search form. Each item has a check box and a combo box. I want the query to look at the check box. If the box is checked, I want the query to use the value in the combo box. If the check box is UN checked, I want the query to ignore this field and return all values. I thought this was simple, until I tried to do it!

Here is my code, placed in the Field-name criteria box of the query:

IIf([Forms]![form-name]![checkbox-name]=True,[Forms]![form-name]![combobox-name])

Now I know that the function works, because I can manually add a value:

IIf([Forms]![form-name]![checkbox-name]=True,[Forms]![form-name]![combobox-name],2)

This will return all records where Field-name = 2.

So... my original code should work! Because no "false" condition is stated, the query should be blank, and should return all records. But it doesn't! Truly maddening.

Here are some attempts which did not work:

IIf([Forms]![form-name]![checkbox-name]=True,[Forms]![form-name]![combobox-name],>0)
---> The ">0" caused an error. In fact, I can only specify an integer - all else, including things with operators, do not work.

Working with nulls - it does not seem to recognize an unchecked box as a null.

That's the short version of the story - any and all ideas welcome!

Regards,
-- Mark

 

[EMP]

Put the criteria in a new column in the query grid like this, using the correct field name:-

-----------------------------------
Field: IIf([Forms]![form-name]![checkbox-name]=True, [FieldName]=[Forms]![form-name]![combobox-name], True)

Show: uncheck

Criteria: True
-----------------------------------

which is derived from Jon K's basic criteria format in the sample database forum:-
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

^

 

[MaineWebDad]

Wow - Great! I had seen the earlier post, but missed the significance of giving it its own column in the query. Since this is such a basic need in Access (at least I think so anyway...) I'd like to wrestle with this a moment to explain why this works. Please comment, and maybe nobody else needs to go through this again!

I re-checked the "Show" to see what the query was returning. What I found was that:

| Check Box | Combo Box | Query results for the hidden column
================================================
| Checked | Option yields results | -1 with each result
| Checked | Option has no results | blank, no results
| Un Ck'd | -- NA -- | -1 with each result

So I'm confused... if True is supposed to be "0", why are these results yielding "-1"s?

Thanks,
-- Mark

 

[boblarson]

False is 0, True is -1

 

[MaineWebDad]

Ahhhh... ok... gotcha.
But I'm still a little confused on the logic. The IIf function has two outomes - what to do if the condition is true, and what to do if the condition is false. What does the value-if-false of "True" do? How does the function evaluate the criteria of "True"?
-- Mark

 

[boblarson]

The IIF function has one of two possible outcomes -

1. the condition is true or
2. the condition is false

The first part of the IIF is the condition, the second part (after the first comma) is what to return if the condition is true and the third part is what to return if the condition is false.

How does the function evaluate the criteria of "True"

If you look at it this way -

If I have something that says

"If my checkbox=True" and if the checkbox is checked and bound to a field, the field (literal value of -1) it would evaluate as true, and if it is unchecked it will evaluate as False (0). So, an IIf statement would be:

IIf([mycheckboxfield]=True, "Do whatever if true", "do whatever if false")
but it could be written (with the same results)
IIf([mycheckboxfield]=False,"Do whatever if that is true, do whatever if false")
Now, if the "Do whatever if true" and "do whatever if false" stay in the same locations in the above formulas, the result will be opposite each other. If you reverse the two then the two formulas will actually be identical in essence.

I hope that helps.

 

[MaineWebDad]

Ok - I get the "do whatever is true, do whatever is false." And if it was an expression like SomeVariable=15, then I could get that. If it's something like IIF(GreenVariable>15, RedVariable=5, RedVariable=2). That makes sense.

What I'm having a hard time wrapping my brain around is having the "do if false" statement as "True." What does IIf do with that?

Thansks for helping me get a handle on this,
-- Mark

 

[boblarson]

When you are evaluating something like

If MyVariable has an actual value of TRUE, then if it IS True

IIF(MyVariable=True... will evaluate to True and therefore the first part to do if true will happen.

If your variable MyVariable has an actual value of FALSE then
IIF(MyVariable=True... will evaluate as FALSE - it is false and so it does not equal true.

If your variable MyVariable has an actual value of FALSE and you have the statement
IIF(MyVariable=False... then it will evaluate as TRUE because the statement that MyVariable=False is a true statement.

Does that help?

 

[EMP]

Or we can look at it like this.

We know that when the check box is checked, a record whose field value is equal to the value in the combo box will be returned because the expression [FieldName]=[Forms]![form-name]![combobox-name] for this record will evaluate to True.

Hence when the check box is not checked, we can simply supply True to each record so that every record will be returned.

^

 

[MaineWebDad]

I think I actually get it! But there's a little twist... The form I built this on was supposed to become a subform for the Switchboard form. When I made it a subform, it started asking me to define all the criteria through pop-up boxes, which was understandable. So I went into the code you sent, and changed this:

IIf([Forms]![form(subform)]![checkbox]=True,[variable]=[Forms]![form(subform)]![combobox],True)

to this:

IIf([Forms]![switchboard]![form(subform)]![checkbox]=True,[variable]=[Forms]![switchboard]![form(subform)]![combobox],True)

I figured this would do it. But it doesn't. Now, whenever I select any criteria - with the checkbox checked or unchecked, no matter what I do, all the results come back. I get the same results as if I did not have the checkbox checked and it was giving me everything.

Any idea what I've done wrong?
Thanks,
-- Mark

 

[boblarson]

Subform syntax is a bit challenging, even for some more experienced users. I am not totally sure what you are trying to accomplish by this formula because the last part you have in the false part (True) doesn't make sense in the context of the rest of the formula.

Are you quoting your actual form/subform names and your control names in the code posted here? Also, is this in a query or a control source for a control?

 

[MaineWebDad]

Sorry, let me reframe:

Here's what you sent me:

IIf([Forms]![form-name]![checkbox-name]=True, [FieldName]=[Forms]![form-name]![combobox-name], True)

If the form "form-name" is now going to be the subform to a form called "big-form", then here's what I did:

IIf([Forms]![big-form]![form-name]![checkbox-name]=True, [FieldName]=[Forms]![big-form]![form-name]![combobox-name], True)

But the problem is, the query returned all values, no matter what. It behaved like the checkbox wasn't checked, even when it was. Very odd. I'm bummed because if I can't include the "form-name" as a subform of a bigger form, it means every implementation of this solution has to be in its own form.

Regards,
-- Mark

 

[boblarson]

So, I'm assuming that this is criteria for a query then, is that correct?

First of all, based on what you have, you need to have:

MyColumnTitle:IIf(Forms![big-form].[subformCONTROLNAME].Form.[checkbox-name]=TRUE,Forms![big-form].[subformCONTROLNAME].Form.[combobox-name],"whatever the value here")

Now, some explaining needs to happen.
1. I hope you aren't using the dash (-) in names of forms as that can cause you grief.
2. The part that says "subformCONTROLNAME" means the container on the main form that houses your subform. It is usually the same name as your subform, but it is possible it could be named something like "ActiveXCtl3" and if that is the case then you have to refer to ActiveXCtl3 instead of the name of the subform.
3. The part I wrote about "whatever the value here" means that you can't set the value as TRUE because the IIF is not returning a boolean value for the true statement, otherwise you would just put
IIF(Forms!YourMainFormNameHere.YourSubFormContainerNameHere.Form.YourControlNameHere=TRUE,FALSE,TRUE) and not returning a text or numerical value from your combo box.

If you want it to actually say TRUE then you would need to put it in double quotes.

Now, that being said, can you post your database here (compact first, then zip it - it has to be under 393Kb) so I can see what you're trying to do, because I don't think we're actually coming to a point where we understand each other. And, I am thinking that what you are trying to do is actually going to be best accomplished by some other means.

 

[MaineWebDad]

Many thanks for taking the time to work with me on this. I'm just thankful it's part of a forum discussion - hopefully it will save some other users some time and energy!

Right - this is a query. The query goes and looks at a checkbox and a combo box. If the box is checked, the query will use the value in the combo box next door as the criteria. If the box is unchecked, the query will return all records - the same as if you were looking at the table itself.

Boy you weren't kidding when you said that the subform aspect complicates things! Aye Carumba! I'll give your code a try and see what I come up with.

Nope - not using dashes in the actual control and field names, but am using_underscores.

As mentioned before, your solution worked perfectly when it was just a stand alone form. Now that I'm trying to make it a subform to a parent form, I'm running into issues. It may be useful to note that the parent form is the Switchboard. There is no bounding or connection of any kind between the Switchboard and the (now) subform.

I would love to send a copy of the database, but it's currently 41 meg. I teach high school English, and this database started out as a tool for making rubrics to grade my students. Now I'm adding other things to it. This is a contact log for talking to parents. This summer, I'm adding a lesson planner and grading program. I could try to make a copy and strip out the data to make it lighter, but that might take some time, and if I strip out some of the decode tables, then some of the combo boxes wouldn't make much sense.

I think (hope at least) that we're close to putting this to bed. If it ends up being too much trouble making this a subform to the Switchboard, I'll just put a button on the switchboard to open this form up on its own, as I've been doing while I implemented your earlier solution.

Regards,
-- Mark

 

[boblarson]

Okay, with that explanation I think we're on to something. I think we've been barking up the wrong tree. I think you should put the criteria into code that is on the button.

So, something like this:

Private Sub Command1_Click()
Dim strSQL as String
    
   If Me.CheckBoxName = True Then
        strSQL = "SELECT * From YourTableName WHERE FieldName= '" & Me.ComboBox & ";"
   Else
        strSQL = SELECT * FROM YourTableName
   End IF
      Forms!YourMainFormName.YourSubFormContainerName.Form.RecordSource = strSQL
      Forms!YourMainFormName.Form.YourSubFormContainerName.Requery

End Sub

 

[MaineWebDad]

Wow - that's a slick solution! There's only one concern that I have. At some point in the future, I'll probably want to add other criteria. For example, if this were a database for Christmas trees, the code you sent me would allow me to check the checkbox, then use the combo box to select "Douglas Fir" as the type of tree. Or I could uncheck the checkbox and see all the tree types in the database.

But in the future, I might like to be able to search for "Douglas Fir" trees "from Maine" which are "already decorated." That's three different criteria.

How hard would it be to expand your code to cover two or more criteria? (I don't mean to be difficult... I just figure I have this one moment to define this thing, and the code which comes out of this forum will probably be how I handle this code situation for the rest of my life. I want to get it right!)

Regards,
-- Mark

 

[Bat17]

this should give you an idea of how to build a where clause on the fly

Private Sub Command1_Click()
Dim strSQL As String

strSQL = "SELECT * FROM YourTableName WHERE"
    
   If Me.CheckBoxName1 = True Then
        strSQL = strSQL & " FieldName= '" & Me.ComboBox1 & "' AND"
   End If
   If Me.CheckBoxName2 = True Then
        strSQL = strSQL & " FieldName= '" & Me.ComboBox2 & "' AND"
   End If
   If Me.CheckBoxName3 = True Then
        strSQL = strSQL & " FieldName= '" & Me.ComboBox3 & "' AND"
   End If
   
' check if any clauses have been added
If Len(strSQL) = 33 Then ' no clauses added
    strSQL = Left(strSQL, 27) ' chop off " WHERE"
Else
    strSQL = Left(strSQL, Len(strSQL) - 4) ' chop off " And"
End If

      Forms!YourMainFormName.YourSubFormContainerName.Form.RecordSource = strSQL
      Forms!YourMainFormName.Form.YourSubFormContainerName.Requery

End Sub

HTH

Peter

 

[boblarson]

I have an idea for you and I'm building a sample. I have to go out for a while today, so I won't be able to finish it right away. I'll get back to you.

Basically it will be adding a table for criteria (which controls to reference and the field associated with it) and then we can build the SQL query dynamically and then if you add other controls to limit criteria, it is easy to do without changing code.

 

[MaineWebDad]

Peter and Bob - thank you so much!
Bob, I think building an example is a great idea! I really appreciate your help in this!
Regards,
-- Mark