DanaCurnock
asked on
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?
ASKER
paelo
I got error messages when I inserted your code?
I got error messages when I inserted your code?
Please type the full error message(s) in this question.
ASKER
jimhorn
In the report, it shows #Error in every line.
In the report, it shows #Error in every line.
This should produce the same result:
IIf (IsNull([CompanyName], [ContactMergedName], [CompanyName])
IIf (IsNull([CompanyName], [ContactMergedName], [CompanyName])
Try this (although Nz should work)
=Iif(IsNull([CompanyName]) , [ContractMergedName], [CompanyName])
=Iif(IsNull([CompanyName])
ASKER
jimhorn:
=IIf(IsNull([CompanyName]) ,[ContactM ergedName] ,"")
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 ]),[Contac tMergedNam e],[Compan yName])
=IIf(IsNull([CompanyName])
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
jimhorn
I am getting #Error values (Circular Reference) for every record when I use:
=IIf(Nz([CompanyName]),[Co ntactMerge dName],[Co mpanyName] )
or
=IIf(IsNull([CompanyName]) ,[ContactM ergedName] ,[CompanyN ame])
rptInvNumbersUsed2 recordsource
SELECT tblUsedInvoices.CustomerID , tblUsedInvoices.InvoiceNum ber, tblUsedInvoices.DecInv, tblUsedInvoices.JanInv, tblUsedInvoices.FebInv, tblUsedInvoices.MarInv, tblUsedInvoices.AprInv, tblContacts.Commercial, tblContacts.CompanyName, tblWinterPrograms.ContactM ergedName
FROM tblUsedInvoices INNER JOIN (tblWinterPrograms INNER JOIN tblContacts ON tblWinterPrograms.ContactI D = tblContacts.ContactID) ON tblUsedInvoices.CustomerID = tblWinterPrograms.Customer ID
WHERE (((tblUsedInvoices.Invoice Number)>11 767));
I am getting #Error values (Circular Reference) for every record when I use:
=IIf(Nz([CompanyName]),[Co
or
=IIf(IsNull([CompanyName])
rptInvNumbersUsed2 recordsource
SELECT tblUsedInvoices.CustomerID
FROM tblUsedInvoices INNER JOIN (tblWinterPrograms INNER JOIN tblContacts ON tblWinterPrograms.ContactI
WHERE (((tblUsedInvoices.Invoice
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Stephen_Perrett
That worked...thanks.
That worked...thanks.
=Nz([CompanyName], [ContactMergedName])
-Paul.