Criteria for Select Query

[papadega3000]

Hello,

I am struggling writing criteria that is based off of multiple combo boxes in a search form. I basically have a form setup in several pages the first page being a search page. This page consists of three combo boxes which are populated from the tables. These three boxes are all related. The way the search is setup at the moment is if the combo boxes are Null then they report all the data in the fields but if something is selected then the query gets requeried and displays the data based on that criteria.

What I need to have it do is if it is Null report everything but if something is selected in the first box that selection will also be apart of the second criteria and third criteria.

What I have written at this time is;
First Box:
IIf(IsNull([Forms]![FormName]![Combo1]),[Data1],[Forms]![FormName]![Combo1])
Second Box:
IIf(IsNull([Forms]![FormName]![Combo2]),[Data2],[Forms]![FormName]![Combo2] )
Third Box:
IIf(IsNull([Forms]![FormName]![Combo3]),[Data3],[Forms]![FormName]![Combo3] )

My intentions are too write something that allows the criteria to use the other values in each combo box. The problem I have is it keeps spitting an error out at me which says I have the wrong number of arguments.

Here is what I am trying to accompolish.

PsuedoCode:

If (Combo1 =Null) {
Select all values of Data1
Else {
Select all values of Data1 with match value of Combo1
}
If ( Combo2 = Null) {
If( Combo1= Null){
Select all values of Data2
}
}
Else{
Select all values of Data2 with matching value of Combo1 and Combo2
}
}


Any help will be greatly appreciated.

Thanks!

 

[ajetrumpet]

My intentions are too write something that allows the criteria to use the other values in each combo box. The problem I have is it keeps spitting an error out at me which says I have the wrong number of arguments.

You're missing the "value if false" section of the IIF functions.

You're psuedo code says something like displaying all values of field "A" if "A" is null AND only records with values of box "B" if you select criteria in box "B". You can't do this in one single query.

I don't think you can do what you want without a restructure of some kind, if even that. What you can do though with this setup is pull records based on ANY criteria you have placed in any ONE or combination of the combo boxes, and if all three combos are null, then pull every record. To do this, type....

WHERE (([field1]=Forms![Form1]![combo1] OR Forms![Form1]![combo1] IS NULL) 

AND ([field2]=Forms![Form2]![combo2] OR Forms![Form1]![combo1] IS NULL) 

AND ([field3]=Forms![Form3]![combo3] OR Forms![Form3]![combo3] IS NULL));

 

[Brianwarnock]

A useful link

Brian]

 

[papadega3000]

Thanks for your input on my issue.

In regards to the first reply. I did notice that problem in my code. I actually using a similar approach I "re-worked" the query so that I was able to get the search to work the way I want. At this point I got the query to look at each of the combo boxes two set the criteria for the other two combo boxes and the same approach should work as well.

Thanks again for the information both posts provide I will keep this forum post for my reference as I continue to finish this project.