Problem with displaying Null field in a query criteria

[marcin2k]

here is my criteria so far,
IIf(Nz([Forms]![fLAMPREditProd]![txtInt2ID],0)=0,Null,[Forms]![fLAMPREditProd]![txtInt2ID])

The False part of it works but whenever the txtInt2ID textbox is empty then the True part does not display Null records. It does not let me put "Is Null" because its a Number field, any idea on the error or a solution to it?
Thanks

 

[Remou]

Hi
Do you want it to _say_ Null records? I am a little confused, because you are using Nz to get rid of nulls.

To say Null:
IIf(Nz([Forms]![fLAMPREditProd]![txtInt2ID],0)=0,"Null",[Forms]![fLAMPREditProd]![txtInt2ID])

 

[AppStaff]

try iif(Forms!fLAMPREditProd![txtInt2ID]>=0,Forms!fLAMPREditProd![txtInt2ID],"Null")

As long as the value can't be negative I think this will work for you. This doesn't address your specific issue but it could be a quick work around if your not tied into determining the exact problem.

 

[PHV]

Something like this ?
WHERE Nz([your number field],0)=Nz([Forms]![fLAMPREditProd]![txtInt2ID],0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[marcin2k]

Its not leeting me use "Null" saying its too complex to evaluate. I think it has to do with that this field is a number field instead of a string field

 

[marcin2k]

Thank you all, Got it to work with your code PHV, i basically just put
Nz([your number field],0)=Nz([Forms]![fLAMPREditProd]![txtInt2ID],0)
in my criteria and it works like a charm