If Null Then Another Value...

Change the control source to:

=Nz([CompanyName], [ContactMergedName])

-Paul.

paelo

I got error messages when I inserted your code?

Please type the full error message(s) in this question.

jimhorn

In the report, it shows #Error in every line.

This should produce the same result:

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

Try this (although Nz should work)

=Iif(IsNull([CompanyName]), [ContractMergedName], [CompanyName])

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

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

Stephen_Perrett


That worked...thanks.