Need help with iif stmt in text box

[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

 

[seeker]

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

 

[Marshall Barton]

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.

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

 

[Jerry]

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

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.

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