If Null Then Another Value...

I have a textbox in a rptInvNumbersUsed2 detail section that is linked to [CompanyName]. I want to be able to show the data from [ContactMergedName] if there is no values in [CompanyName]. How can I do this?
DanaCurnockAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
=IIf(IsNull([CompanyName]),[ContactMergedName],"")
Will display a blank if CompanyName is not null, regardless if what's in ContactMergedName.

=Iif(IsNull([CompanyName]), [ContractMergedName], [CompanyName])
Will display CompanyName if it has a value, regardles of what's in ContactMergedName.
0
 
paeloCommented:
Change the control source to:

=Nz([CompanyName], [ContactMergedName])

-Paul.
0
 
DanaCurnockAuthor Commented:
paelo

I got error messages when I inserted your code?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Please type the full error message(s) in this question.
0
 
DanaCurnockAuthor Commented:
jimhorn

In the report, it shows #Error in every line.
0
 
FeyoCommented:
This should produce the same result:

IIf (IsNull([CompanyName], [ContactMergedName], [CompanyName])
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Try this (although Nz should work)

=Iif(IsNull([CompanyName]), [ContractMergedName], [CompanyName])
0
 
DanaCurnockAuthor Commented:
jimhorn:

=IIf(IsNull([CompanyName]),[ContactMergedName],"")

I must not be taking into consideration something because all lines are blank?

I know that only 8 records have a [ContactMergedName]

I did this and got what I wanted but in the records that have [CompanyName] I got #Error.
The second thing that I found that where there is both a [CompanyName] and a [ContractMergedName], I got [ContractMergedName].

=IIf(Nz([ContactMergedName]),[ContactMergedName],[CompanyName])
0
 
DanaCurnockAuthor Commented:
jimhorn

I am getting #Error values (Circular Reference) for every record when I use:
=IIf(Nz([CompanyName]),[ContactMergedName],[CompanyName])

or

=IIf(IsNull([CompanyName]),[ContactMergedName],[CompanyName])

rptInvNumbersUsed2 recordsource

SELECT tblUsedInvoices.CustomerID, tblUsedInvoices.InvoiceNumber, tblUsedInvoices.DecInv, tblUsedInvoices.JanInv, tblUsedInvoices.FebInv, tblUsedInvoices.MarInv, tblUsedInvoices.AprInv, tblContacts.Commercial, tblContacts.CompanyName, tblWinterPrograms.ContactMergedName
FROM tblUsedInvoices INNER JOIN (tblWinterPrograms INNER JOIN tblContacts ON tblWinterPrograms.ContactID = tblContacts.ContactID) ON tblUsedInvoices.CustomerID = tblWinterPrograms.CustomerID
WHERE (((tblUsedInvoices.InvoiceNumber)>11767));
0
 
Stephen_PerrettCommented:
Hi Dana,

Change the names of your text boxes in report from

e.g.

CompanyName
ContactMergedName
to

txtCompanyName
txtContactMergedName

This should overcome circular reference problem

Steve
0
 
DanaCurnockAuthor Commented:
Stephen_Perrett


That worked...thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.