Combo Box IIF Expression Help

[MintChipMadness]

Hi Everyone,



I am having issues with an expression in a form. I have two combo boxes in my form named ComboA and ComboB. What I am trying to do is sort the list of ComboB by a selection in ComboA. If no value is given for ComboA, I would like the criteria to be left blank so I pull all values. I will list a few things I have tried below. Thank you for your help.

IIF([ComboA] = "", "", [ComboA])
IIF([ComboA] is not null, [ComboA], "")

[orange]

iff is really iif

You should get familiar with the IsNull function
http://www.techonthenet.com/access/f...ced/isnull.php

Understand the difference between NULL and zero length string
see
http://answers.yahoo.com/question/in...8045303AAZu8fM

[jameslarsen]

My favorite way to modify a combo box depending on the value of another combo box is to use the "AfterUpdate" property of the first combo box to change the "rowsource" of the second combo box. I created a quick database to change the filter of one box depending on the selection in another, but you could modify it to just change the "ORDER BY" clause of the rowsource SQL statement. Let me know if this helps you.

Multiple Combo Boxes.accdb

James A. Larsen
Database Architect, Data Analyst, and Business Intelligence Specialist
james.larsen42@gmail.com

[MintChipMadness]

Orange,
Thank you for the links. I edited my post to fix the typos for iif. What can I say, I wrote it quickly. I did make sure I didn't type it into the database like that and I did it correctly. I did a test and I figured out when a combo box is empty it is a zero length string. I will have to try it again to see where I went wrong in the expression.

[MintChipMadness]

James,
Thank you for your help. Unfortunately, I don't know VBA so I won't be able to apply your code to my database. I should learn that someday. I am trying to put my database together with simple expressions and macros.

[MintChipMadness]

I am still having problems. I believe the criteria would be IIF([Forms]![SomeForm]![ComboA] = "", "", [Forms]![SomeForm]![ComboA). A return of false works correctly but the true doesn't. I think because I am putting a zero length string in and the query is looking for all records with a zero length string. How do I enter in the express to make it quit and not sort by anything if my iif statement is true?

[orange]

Can you show some of the code that uses the output of the iif statement?
I have acc2003 so accdb does me no good.

[MintChipMadness]

Orange,

I am not sure if I can put the code here. I do have some addition information though. I was incorrect when I said the combo box was a zero length string when empty. I tested it again and it is a null value. I am not sure why the test came out wrong the first time. Knowing that the combo box is null value when empty, I tried a few more expressions and they only half worked. I listed them below. When I have a value in the combo box it works but when it is null I get no value in my query when I should get all the records. I don't know what to do. I did try to get results in zero length string just to try everything. Thanks again for all your help.

IIf(IsNull([ComboA]),null,[ComboA])

IIf(IsNull([ComboA]),"",[ComboA])

IIf(IsNull([ComboA])= False,[ComboA],null)

IIf(IsNull([ComboA])= False,[ComboA],"")

IIF(IsNull([ComboA])= False,[ComboA])

[orange]

What is the recordsource of the combo ( query sql if you have same)?
Why is there no value in the combo?
Have you tried doing a DCount on the recordsource to see if there are records/values for the combo?

Have you seen
http://www.techonthenet.com/access/f...dvanced/nz.php

[MintChipMadness]

The reason there might be no value in the combo list is because I want it to filter a certain way. I have two combo boxes, ComboA and ComboB. ComboA queries TableA and ComboB queries TableB with TableA to filter. TableA is the parent of TableB. What I am trying to do is when I use ComboA it filters the results in ComboB but when I leave it blank, I want it to not filter using ComboA and give all the records.

I had another breakthrough but still can't get it to work. I tried putting in only the function like "*" in the filter conditions and it worked! So both true and false work of the iif statement but when I put them together they don't. I did some more research with null values and I found the combo boxes start off null and when they are changed the null is removed. Do to this I created an update macro to check if the box is ever "" (Zero Length String) and if so, to change it back to null. Now that all that is figured out, I still can't get it to work. A false result in the iif function works but a true result doesn't. Anyone have any ideas? Below are some expressions I tried in the criteria field of the query builder.


iif([ComboA] = null, like "*", [ComboA])
iif(isnull([ComboA]), like "*", [ComboA])

[MintChipMadness]

I figured it out. I put the expression below in the criteria box. No iif statement needed.

Like "*" & [ComboA] & "*"

[orange]

Glad you got it solved