iif / switch problems

C

cherman

I have a check box on a form. If it is checked, I want to filter a query
(Amount field) to show all records > 0. Otherwise I want to show all the
records. I’ve tried to use iif and switch, but both give me the wrong
results. Here is my code.

IIf([Forms]![frmEncumbranceBalancesCriteria]![chkGreaterThanZero]=-1,[qryEncBalance1].[Amount]>0,[qryEncBalance1].[Amount])

Switch([Forms]![frmEncumbranceBalancesCriteria]![chkGreaterThanZero]=0,[qryEncBalance1].[Amount],[Forms]![frmEncumbranceBalancesCriteria]![chkGreaterThanZero]=-1,[qryEncBalance1].[Amount]>0)

Can someone suggest the right way to do this? It seems that when the check
box is checked, I get all records equaling zero, not all records greater than
zero.

Thanks,
Clint Herman
 
K

Ken Snell [MVP]

You cannot use IIf nor Switch to "produce" a logic criterion statement this
way; both are used to produce a value, which can be a fixed number or the
result of a logical test.

Try this in your query (this is generic):

SELECT * FROM MyTableName
WHERE IIf([Forms]![frmEncumbranceBalancesCriteria]![chkGreaterThanZero]
= -1,
[qryEncBalance1].[Amount]>0,Nz([qryEncBalance1].[Amount],0) =
Nz([qryEncBalance1].[Amount],0) ) = True;
 
C

cherman

Perfecto! Thank you for your response.

Clint

Ken Snell said:
You cannot use IIf nor Switch to "produce" a logic criterion statement this
way; both are used to produce a value, which can be a fixed number or the
result of a logical test.

Try this in your query (this is generic):

SELECT * FROM MyTableName
WHERE IIf([Forms]![frmEncumbranceBalancesCriteria]![chkGreaterThanZero]
= -1,
[qryEncBalance1].[Amount]>0,Nz([qryEncBalance1].[Amount],0) =
Nz([qryEncBalance1].[Amount],0) ) = True;

--

Ken Snell
<MS ACCESS MVP>

cherman said:
I have a check box on a form. If it is checked, I want to filter a query
(Amount field) to show all records > 0. Otherwise I want to show all the
records. I've tried to use iif and switch, but both give me the wrong
results. Here is my code.

IIf([Forms]![frmEncumbranceBalancesCriteria]![chkGreaterThanZero]=-1,[qryEncBalance1].[Amount]>0,[qryEncBalance1].[Amount])

Switch([Forms]![frmEncumbranceBalancesCriteria]![chkGreaterThanZero]=0,[qryEncBalance1].[Amount],[Forms]![frmEncumbranceBalancesCriteria]![chkGreaterThanZero]=-1,[qryEncBalance1].[Amount]>0)

Can someone suggest the right way to do this? It seems that when the check
box is checked, I get all records equaling zero, not all records greater
than
zero.

Thanks,
Clint Herman
Click to expand...
Click to expand...
 
Top