Need help with iif stmt in text box

  • Thread starter Thread starter Jerry
  • Start date Start date
J

Jerry

I have the following expression as the control source in a text box on a
report, which is generated from a form.

Nz(DLookUp("[Carrier]","[Carrier]","[CarrierID]=" & [Carrier]),""))

I get the old "#error" when the field CARRIER, the data for which comes from
the form, is blank. I want the box on the report to be blank when the field
CARRIER has no data entered in it. I cannot create the correct IIF stmt that
will work. Help!!
thanks
Jerry Bennett
 
it looks like you have one too many parenthesis. take out the one right
after & [Carrier] and before the ,. See if that takes away the error.

seeker
 
Jerry said:
I have the following expression as the control source in a text box on a
report, which is generated from a form.

Nz(DLookUp("[Carrier]","[Carrier]","[CarrierID]=" & [Carrier]),""))

I get the old "#error" when the field CARRIER, the data for which comes from
the form, is blank. I want the box on the report to be blank when the field
CARRIER has no data entered in it. I cannot create the correct IIF stmt that
will work.
Click to expand...


In addition to the extra ), when the value of the Carrier
text box is "blank" (either "" or Null), then the third
argument to DLookup ends up being:
[CarrierID]=
which is an invalid expression.

Try adding another Nz:

Nz(DLookUp("[Carrier]","[Carrier]","[CarrierID]=" &
Nz([Carrier], "")),"")

Are you sure you want the result to be a zero length string
when Carrier is "blank"? It is normal for a "blank" field
to contain Null so I suggest that you try it without the
outer NZ
 
Marsh
Thanks very much for the reply; I copied the expression with that extra
paren but didn't notice it; that paren was actually deleted so it has nothing
to do with the problem. But i will try your second suggestion, let you know.
thanks
jerry bennett
Mass

Marshall Barton said:
Jerry said:
I have the following expression as the control source in a text box on a
report, which is generated from a form.

Nz(DLookUp("[Carrier]","[Carrier]","[CarrierID]=" & [Carrier]),""))

I get the old "#error" when the field CARRIER, the data for which comes from
the form, is blank. I want the box on the report to be blank when the field
CARRIER has no data entered in it. I cannot create the correct IIF stmt that
will work.
Click to expand...


In addition to the extra ), when the value of the Carrier
text box is "blank" (either "" or Null), then the third
argument to DLookup ends up being:
[CarrierID]=
which is an invalid expression.

Try adding another Nz:

Nz(DLookUp("[Carrier]","[Carrier]","[CarrierID]=" &
Nz([Carrier], "")),"")

Are you sure you want the result to be a zero length string
when Carrier is "blank"? It is normal for a "blank" field
to contain Null so I suggest that you try it without the
outer NZ
Click to expand...
 
Back
Top