Using Between and IIF in a query paramater

[todmac]

Hello All,
I have the following query criteria and for some reason it will not work. It only brings up the records that have a value of "0"

[CJ_London]

I have the following query criteria

And the criteria is???

[todmac]

Sorry, I posted before I got a change to paste it in.

Between IIf(IsNull([forms]![reports].[Amount_Claimed_Before_Tax_Start]),"0",[Forms]![Reports].[Amount_Claimed_Before_Tax_Start]) And IIf(IsNull([Forms]![Reports].[Amount_Claimed_Before_Tax_End]),"10000000",[Forms]![Reports].[Amount_Claimed_Before_tax_End])

[pbaldy]

Try

Between Nz([forms]![reports].[Amount_Claimed_Before_Tax_Start]),"0") And Nz([Forms]![Reports].[Amount_Claimed_Before_Tax_End]),"10000000")

My gut feeling is that you don't want quotes around the numbers though.

[CJ_London]

think you've got a couple of extra brackets there Paul

Between Nz([forms]![reports].[Amount_Claimed_Before_Tax_Start]),"0") And Nz([Forms]![Reports].[Amount_Claimed_Before_Tax_End]),"10000000")

don't think you need the ones in red

And I agree about not needing the quotes around the numbers

[pbaldy]

Oops, you are correct. Lack of diligence during cut/paste operations.

[CJ_London]

I do it all the time! Think you picked up one of mine the other day