Question:Text box on report

  • Thread starter Thread starter Ron Weaver
  • Start date Start date
R

Ron Weaver

My report has a text box that shows CompanyName. I need this text box to show
ContactName if CompanyName is null. Thanks for any help on this.
Ron
 
My report has a text box that shows CompanyName. I need this text box to show
ContactName if CompanyName is null. Thanks for any help on this.
Ron
Click to expand...

Set its Control Source to

=NZ([CompanyName], [ContactName])

The NZ function will return its first argument if it is non-NULL, and its
second argument otherwise.
 
John, thanks for the reply.
This works fine if control "CompanyName " is not null, but will not insert
the control contents of "ContactName" if it is. I have double checked the
control names and the source code you provided. When I delete the company
name, "error" shows on the report. I can't figure out what I'm doing wrong.


John W. Vinson said:
My report has a text box that shows CompanyName. I need this text box to show
ContactName if CompanyName is null. Thanks for any help on this.
Ron
Click to expand...

Set its Control Source to

=NZ([CompanyName], [ContactName])

The NZ function will return its first argument if it is non-NULL, and its
second argument otherwise.
Click to expand...
 
John, thanks for the reply.
This works fine if control "CompanyName " is not null, but will not insert
the control contents of "ContactName" if it is. I have double checked the
control names and the source code you provided. When I delete the company
name, "error" shows on the report. I can't figure out what I'm doing wrong.
Click to expand...

What is the Recordsource of your report? Could you post the SQL view? Is there
a field in that recordsource for ContactName, and/or is there a control on the
form named ContactName? If you have both a field and a textbox, that could be
the problem: try changing the name of the textbox to txtContactName, for
example, so it's unambiguous.
 
To simplify this I created another form with just 3 text boxes. "txt
CompanyName", "txtContactName"' and a third text box to display the Company
Name or the Contact Name. The code(in the third box) still does't work.
=NZ([txtCompanyName],[txtContactName])
I finally used a fourth text box and tried this code:
=IIF(IsNull([txtCompanyName],[txtContactName],[txtCompanyName[). That worked.
I am curious why the NZ code didn't work. Here is the SQL:SELECT
Customers.CompanyName, [ContactFirstName] & " " & [ContactLastName] AS
ContactName
FROM Customers;
Thanks
 
I finally used a fourth text box and tried this code:
=IIF(IsNull([txtCompanyName],[txtContactName],[txtCompanyName[). That worked.
I am curious why the NZ code didn't work.
Click to expand...

So am I! I don't have a clue why IIF() would work and NZ would not, in this
context. Have you checked References?
 
That second code should have read:
=IF(IsNull([txtCompanyName]),[txtContactName],[txtCompanyName]). See the
parenthesis after [txtCompanyName]
Sorry about that.
Ron Weaver said:
To simplify this I created another form with just 3 text boxes. "txt
CompanyName", "txtContactName"' and a third text box to display the Company
Name or the Contact Name. The code(in the third box) still does't work.
=NZ([txtCompanyName],[txtContactName])
I finally used a fourth text box and tried this code:
=IIF(IsNull([txtCompanyName],[txtContactName],[txtCompanyName[). That worked.
I am curious why the NZ code didn't work. Here is the SQL:SELECT
Customers.CompanyName, [ContactFirstName] & " " & [ContactLastName] AS
ContactName
FROM Customers;
Thanks

John W. Vinson said:
What is the Recordsource of your report? Could you post the SQL view? Is there
a field in that recordsource for ContactName, and/or is there a control on the
form named ContactName? If you have both a field and a textbox, that could be
the problem: try changing the name of the textbox to txtContactName, for
example, so it's unambiguous.
Click to expand...
Click to expand...
 
I believe the references are correct They are as follows: Visual Basics For
Applications, Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object
Library, OLE Automation, Microsoft Visual Basic for Applications
Extensibility 5.3, and Button Painter 2000 v1 6. These are the only ones
checked.
Beats me!
John W. Vinson said:
I finally used a fourth text box and tried this code:
=IIF(IsNull([txtCompanyName],[txtContactName],[txtCompanyName[). That worked.
I am curious why the NZ code didn't work.
Click to expand...

So am I! I don't have a clue why IIF() would work and NZ would not, in this
context. Have you checked References?
Click to expand...
 
I believe the references are correct They are as follows: Visual Basics For
Applications, Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object
Library, OLE Automation, Microsoft Visual Basic for Applications
Extensibility 5.3, and Button Painter 2000 v1 6. These are the only ones
checked.
Click to expand...

And none are marked Missing?

If you're not actually *using* the "extra" references - Extensibility and
Button Painter - you might want to try removing them to see if that has an
effect.
 
For your info: None are marked missing. I unchecked the two you suggested
(don't have a clue what they are for). That didn't help. I will just use the
IIF() for now. If you think of anything else please let me know. I will keep
looking for a solution.
Thanks again.
 
Back
Top