• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 914
  • Last Modified:

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?
0
DanaCurnock
Asked:
DanaCurnock
2 Solutions
 
paeloCommented:
Change the control source to:

=Nz([CompanyName], [ContactMergedName])

-Paul.
0
 
DanaCurnockAuthor Commented:
paelo

I got error messages when I inserted your code?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Please type the full error message(s) in this question.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now