Need to make IIF stmt work in text box with expression

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

Jerry

I have a text box on a report with the following expression as the control
source: Nz(DLookUp("[Carrier]","[Carrier]","[CarrierID]=" & [Carrier]),"")).
The record source for the report is a query. I get the old standard "#
error" result when the field CARRIER is blank on the form from which the data
for the query is obtained. I have tried many IIF stmts to make the result
blank when CARRIER is blank to no avail. Can someone write the correct
expression for me?
Thanks
Jerry Bennett
 
Have you considered adding the Carrier table to your report's record source
query with a LEFT JOIN? You could then just set the control source of a text
box to Carrier which would be much more efficient.
 
Actually, this expression was written by an "access programmer", and i
thought it might be too complicated. I know how to add a table to the query,
but how do i specify it as a "left join"???
thanks
Jerry

Duane Hookom said:
Have you considered adding the Carrier table to your report's record source
query with a LEFT JOIN? You could then just set the control source of a text
box to Carrier which would be much more efficient.


--
Duane Hookom
Microsoft Access MVP


Jerry said:
I have a text box on a report with the following expression as the control
source: Nz(DLookUp("[Carrier]","[Carrier]","[CarrierID]=" & [Carrier]),"")).
The record source for the report is a query. I get the old standard "#
error" result when the field CARRIER is blank on the form from which the data
for the query is obtained. I have tried many IIF stmts to make the result
blank when CARRIER is blank to no avail. Can someone write the correct
expression for me?
Thanks
Jerry Bennett
Click to expand...
Click to expand...
 
Hopefully this isn't a repeat.

You open the report's record source in design view and add the Carrier
table. Then join the Carrier field to the CarrierID field. Double-click the
new join line to display its properties. Select the option that displays all
records from the original table with the Carrier field.
--
Duane Hookom
Microsoft Access MVP


Jerry said:
Actually, this expression was written by an "access programmer", and i
thought it might be too complicated. I know how to add a table to the query,
but how do i specify it as a "left join"???
thanks
Jerry

Duane Hookom said:
Have you considered adding the Carrier table to your report's record source
query with a LEFT JOIN? You could then just set the control source of a text
box to Carrier which would be much more efficient.


--
Duane Hookom
Microsoft Access MVP


Jerry said:
I have a text box on a report with the following expression as the control
source: Nz(DLookUp("[Carrier]","[Carrier]","[CarrierID]=" & [Carrier]),"")).
The record source for the report is a query. I get the old standard "#
error" result when the field CARRIER is blank on the form from which the data
for the query is obtained. I have tried many IIF stmts to make the result
blank when CARRIER is blank to no avail. Can someone write the correct
expression for me?
Thanks
Jerry Bennett
Click to expand...
Click to expand...
Click to expand...
 
Back
Top