Question:Text box on report

[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

 

[John W. Vinson]

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

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.

 

[Ron Weaver]

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.

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

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 W. Vinson]

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.

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.

 

[Ron Weaver]

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]

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.

So am I! I don't have a clue why IIF() would work and NZ would not, in this
context. Have you checked References?

 

[Ron Weaver]

That second code should have read:
=IF(IsNull([txtCompanyName]),[txtContactName],[txtCompanyName]). See the
parenthesis after [txtCompanyName]
Sorry about that.

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

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.

 

[Ron Weaver]

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!

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.

So am I! I don't have a clue why IIF() would work and NZ would not, in this
context. Have you checked References?

 

[John W. Vinson]

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.

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.

 

[Ron Weaver]

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.