Multiple IIF statements

[DrDance]

Hi all,

I have a table with a column for SEX of horses denoted as g (for gelding), c (for colt), f (for filly) etc etc.

I have a combo box linked to a query and this is what I have as my query syntax.

Like IIf([Forms]![SEARCH]![Combo410]="","*",IIf([Forms]![SEARCH]![Combo410]="Colts","c",IIf([Forms]![SEARCH]![Combo410]="Fillies","f",IIf([Forms]![SEARCH]![Combo410]="Geldings","g",IIf([Forms]![SEARCH]![Combo410]="Horses","h",IIf([Forms]![SEARCH]![Combo410]="Mares","m&quot)))))

This all works fine BUT I would like to add other criteria to the drop down list suchs as "Fillies and Mares" and "Colts, Geldings and Horses" so that the query would include BOTH or ALL these values.

Could someone please suggest a solution so that if "Colts, Geldings and Horses" were selected, then the combo box query would search for "c", "g" AND "h".

I'm at my wits end!

 

[LNBruno]

You might be better off using a Select Case statement like this:

dim sInput as string
sInput = ([Forms]![SEARCH]![Combo410]
Select Case sInput
    Case "Gelding"
        ....do something
    Case "Colt"
        ....do something else
    Case "Filly"
        ....some other action
    Case "Fillies and Mares"
        ....do something new
    Case "Colts, Geldings and Horses"
        ....do new thing
    Case Else
End Select

< M!ke >

 

[jryan224]

Use &quot;c&quot; or &quot;g&quot; or &quot;h&quot;

for example

like IIf([Forms]![SEARCH]![Combo410]=&quot;Colts, Geldings And Horses&quot;, &quot;c&quot; or &quot;g&quot; or &quot;h&quot;, &quot;something else&quot

 

[ESquared]

DrDance,

After a bit of thought, I concluded it may not be very easy to do what you want with a simple [Form].[Control] reference in your query.

First of all, the Like operator is good at specifying one criterion, but it becomes difficult to specify multiple criteria as you are asking. Since you are using a single character for the sex, you might get away with:

Like &quot;[fm]&quot;

for &quot;Fillies and Mares&quot; for example.

Also, if you are going to allow someone to choose any conceivable grouping of sexes, it might be best to use checkboxes instead of a combobox. If you only wanted to allow certain groupings, then I suppose you should stick with the combobox. To do the checkboxes, when the user clicks your Go button, you would build a WHERE statement from each checked box:

If chbFillies then strWhere = strWhere & "Sex='f' OR "
If chbMares then strWhere = strWhere & "Sex='m' OR "
...

strWhere = Left(strWhere,Len(strWhere) - 4) 'trim final " OR "
If strWhere = "" then strWhere = "*" 'Nothing selected, so select all records

Then use this as the WHERE clause in creating a query or as the filter for your report or form.

Also , you could try creating a hidden bound column in your listbox with the criteria you want there. This way you could avoid so many Iif statements.

Bound | Visible
------------------
&quot;f&quot; | Fillies
&quot;m&quot; | Mares
&quot;[fm]&quot; | Fillies and Mares
&quot;*&quot; | Unspecified

Which as a RowSource would look something like

f;Fillies;m;Mares;[fm];Fillies and Mares

Then your statement would simply be:

Like ([Forms]![SEARCH]![Combo410])

But again, this only works because you are using single-letter codes and the Like function

If none of the above works, you may have to more dynamically create your results, with some VB code. LNBruno suggested one example, above.

 

[Rolliee]

Why not use checkboxes and build your SQL with them. Do you need the code to do so?

If check5 = 1 then SQLWhere = SQLWhere & &quot; and [HorseType] = 'g'&quot;

this is unchecked code now. Watch out!

rollie@bwsys.net

 

[Rolliee]

if you want a sample, post your email or email me.

rollie@bwsys.net

 

[ESquared]

Rolliee,

Did you read my post? I said that about checkboxes... complete with sample code almost identical to yours.

 

[Rolliee]

I confess I did not read it. I found a, what I feel to be a better way, by building a checkstring from the chkboxes and running thru the recordset only onoce.

Rollie

 

[ESquared]

Better way than what?

 

[Rolliee]

than I proposed earlier

 

[ESquared]

In this thread? Under the name Rolliee?