IIf IsNull Problem

[Mitchy]

I think this is a simple problem that I just cannot resolve. I am using the expression =IIf(IsNull([Customer PO]),"NEED PO",[Customer PO]) in a report. The result is an error. Customer PO is a text data type, required-no, allow zero length-no,index-no and unicode compression-yes.

 

[traingamer]

I have no trouble using an expression similar to that as the 'control source' for a text box on a report. Is that where you are using it? What error are you getting?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Mitchy]

#error. If I change the arguements to two "text" responses, I always get the false response whether the [customer po] is null or not.

Maybe this is a clue.

Thanks,

 

[PWise]

try

nz([Customer PO],"NEED PO")

 

[terpster]

Have you checked to see if you have any zero length fields that to the naked eye would appear to be NULL?

 

[Mitchy]

I am not exactly sure how to check for zero length strings. I tried the NZ code and got the same error response.

 

[terpster]

Try this,

=IIf(IsNull([Customer PO]) or [Customer PO] = ""),"NEED PO",[Customer PO])

Have you tried to see if this statement works in a regular query? Or if the query that the report is built upon is pulling the correct information (I know probably dumb questions, but it doesn't hurt to ask! )

 

[PWise]

or try this that also checks for spaces

iif(trim([Customer PO])>"",[Customer PO],"NEED PO")

 

[Mitchy]

I really appreciate all of these suggestions but none of them has change the #error output.

I am stumped.

 

[PHV]

And this ?
=IIf(Trim([Customer PO] & "") = ""), "NEED PO", [Customer PO])

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

 

[PWise]

do you have data in the other fields

 

[PHV]

OOps, sorry for the typo:
=IIf(Trim([Customer PO] & "") = "", "NEED PO", [Customer PO])

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

 

[Mitchy]

I change the isnull expression from the report to the query and it works. I don't know why it won't work in the report but I am getting the results I want.

Thanks to all. If anyone knows why it would not work in the report, I would like to know.

Thanks again,