Need to make IIF stmt work in text box with expression

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
 
D

Duane Hookom

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.
 
J

Jerry

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...
 
D

Duane Hookom

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help with iif stmt in text box 3
Why Doesn't This Work 5
IIF in Crosstab Expression 0
Auto fill in text box using iif function 3
IIf in a report based on select query -- problem w/parameters 2
#Error in Expression 2
using iif() in text box to display alternative data fails? 1
Text box with iif and is null statements 2

Top