passing value from a form with IIF statement in a query

[jsrogol]

I have a form with a combo box that returns either a 1 or 2. If 1 is
selected , the query would select the records that corresponds to the person
with an ID of 1. If the value is 2, I want to select everyone who is NOT a 1.

Using that value from the combo, I want to run a query that incorporates
the following IIF statement:

IIf([forms]![dlgRxRptByMonth].[repDoc]=1,1,>1)

If I select the 1 in the combo box it produces the correct data. IF I select
the value 2, the query runs without an error BUT produces no data.

(I actually tried to insert the >1 as the value in the combo box but that
doesn't work either.)

Anyone have a idea why not? Thanks if anyone can help.

 

[Marshall Barton]

I have a form with a combo box that returns either a 1 or 2. If 1 is
selected , the query would select the records that corresponds to the person
with an ID of 1. If the value is 2, I want to select everyone who is NOT a 1.

Using that value from the combo, I want to run a query that incorporates
the following IIF statement:

IIf([forms]![dlgRxRptByMonth].[repDoc]=1,1,>1)

If I select the 1 in the combo box it produces the correct data. IF I select
the value 2, the query runs without an error BUT produces no data.

(I actually tried to insert the >1 as the value in the combo box but that
doesn't work either.)

You can not use a partial expression, only a value. >1 is
not a value.

To get that result you need to put the entire expression in
the IIf
IIf(Forms!dlgRxRptByMonth.repDoc=1, thefield=1,
thefield<>1)

Unfortunatey, that will not be able to use indexing to
optimize the query so it might be (very?) slow. An
alternative would be to use code to construct the query so
there is no IIf:

strSQL = "SELECT ... FROM ... WHERE "
If Me.repDoc =1 Then
strSQL = strSQL & "thefield=1"
Else
strSQL = strSQL & "thefield<>1"
End If
Me.RecordSource = strSQL