IIF in Select Query

[jrizzo77]

Trying to use the IIF expression in conjunction with a combo box in a
form.

Combo Box:
Blue
Red
Green
Blue/Red

In the query I want the results to filter the Color (obviously). For
the specific colors it works fine but for the combination of colors I
can't get it to work.

iif([color]="Blue","blue",iif([color]="Blue/Red","Blue" Or "Red"))

I get the error "the expression is incorrect or to complex"

I have tried various combination's of Like and IN and I can't get
anything to work

Any help would be appreciated.

 

[John W. Vinson]

Trying to use the IIF expression in conjunction with a combo box in a
form.

Combo Box:
Blue
Red
Green
Blue/Red

In the query I want the results to filter the Color (obviously). For
the specific colors it works fine but for the combination of colors I
can't get it to work.

iif([color]="Blue","blue",iif([color]="Blue/Red","Blue" Or "Red"))

I get the error "the expression is incorrect or to complex"

I have tried various combination's of Like and IN and I can't get
anything to work

Any help would be appreciated.

You can't pass operators such as OR with a parameter.

I'd suggest

[Color] = [Forms]![YourForm]![comboboxname] OR
([Forms]![YourForm]![comboboxname] = "Blue/Red" AND [Color] IN ("Blue", "Red")

 

[KenSheridan via AccessMonster.com]

Why not use a multi-select list box? You'd then just need to list the
individual colours in the control, and any single colour or combination of
colours could be selected. It would be possible for the query to reference
the value list as a parameter, but a simpler and better option would be to
open a form or report based on a query without the parameter via a button on
the form, filtering the form or report by means of the WhereCondition
argument of the OpenForm or OpenReport method, e.g. to open a report in print
preview:

Const conREPORT = "YourReportNameGoesHere"
Dim varItem As Variant
Dim strColorList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.lstColors

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strColorList = strColorList & ",""" & ctrl.ItemData(varItem) &
""""
Next varItem

' remove leading comma
strColorList = Mid(strColorList, 2)

strCriteria = "Color In(" & strColorList & ")"

DoCmd.OpenReport conREPORT, _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox "No color selected", vbInformation, "Warning"
End If

BTW to see how a value list can be referenced as a parameter take a look at:

http://support.microsoft.com/kb/100131/en-us

Of the two methods given there the second is more reliable as it would not
incorrectly return rows if a value is a substring of one of the values in the
value list.

Ken Sheridan
Stafford, England

Trying to use the IIF expression in conjunction with a combo box in a
form.

Combo Box:
Blue
Red
Green
Blue/Red

In the query I want the results to filter the Color (obviously). For
the specific colors it works fine but for the combination of colors I
can't get it to work.

iif([color]="Blue","blue",iif([color]="Blue/Red","Blue" Or "Red"))

I get the error "the expression is incorrect or to complex"

I have tried various combination's of Like and IN and I can't get
anything to work

Any help would be appreciated.

 

[jrizzo77]

Trying to use the IIF expression in conjunction with a combo box in a
form.

Combo Box:
Blue
Red
Green
Blue/Red

In the query I want the results to filter the Color (obviously).  For
the specific colors it works fine but for the combination of colors I
can't get it to work.

iif([color]="Blue","blue",iif([color]="Blue/Red","Blue" Or "Red"))

I get the error "the expression is incorrect or to complex"

I have tried various combination's of Like and IN and I can't get
anything to work

Any help would be appreciated.

You can't pass operators such as OR with a parameter.

I'd suggest

[Color] = [Forms]![YourForm]![comboboxname] OR
([Forms]![YourForm]![comboboxname] = "Blue/Red" AND [Color] IN ("Blue","Red")

thanks. that works perfect!