Add info from another field if present field is empty

[glat]

I would like to have a field in my report whereby if one field is empty, information from another field is inserted as it has to be either one or the other.

I have tried

=IIf([RefNo] Is Null,[AdminRef])

All it seems to do is add in the AdminRef. Any help much appreciated. I'm sure I've done this before but cannot find or remember how!

[PhilOfWalton]

What you have done is told it what to do if RefNo is Null.
You haven't told it what to do if there is a valid RefNo.

 IIf(IsNull([RefNo]), [AdminRef],[RefNo])

Phil

[NeoPa]

Or, more simply, :

Nz([RefNo]), [AdminRef])

[glat]

Phil, can more than one field be added or is it limited to two?

[PhilOfWalton]

Yes, but you're not helping yourself by not stating whether this is a calculated field on your report, or whether it is the underlying query.

What specifically do you want to do?

Phil

[NeoPa]

... and with fifty posts now you should be beyond half-considered posts that waste time Glat.

If you have multiple fields you want to present in the order of whichever is come across first shows, then with fields [A], [B], [C] & [D] you would use :

Nz([A],Nz([B],Nz([C],[D])))

[glat]

Phil it is in an unbound text box in the report. It is not calculated.

[glat]

Sorry NeoPa.

I tried your Nz option however all I kept getting was #Type in my text box.

[PhilOfWalton]

Well, if it's not bound to a field in the report's underlying query, and it's not calculated, where is it getting any information from????

You have not answered my question "What specifically do you want to do?"

Phil

[glat]

The report is based on a query of archived files. Different files have different references and file names; TenantRef, AdminRef; FileDesc, AdminName. I am trying to have all the file references in one text box and all the file names in another text box so the report can fit on a landscape A4 page.

[PhilOfWalton]

OK, Glat

Can you post the SQL for the report's RecordSource

Phil

[glat]

Hi Phil,its

SELECT [Archived Files].TenantRef, [Archived Files].From, [Archived Files].To, [Archived Files].Volumes, [Archived Files].FileNo, [Archived Files].Comments, [Archived Files].FileType, Property.PropertyRef, [Archived Files].Destroyed, Property.Town, Tenant.Company, Property.AddressLine1, Property.AddressLine2, Property.AddressLine3, Property.AddressLine4, Property.PostCode, Admin.AdminName, Admin.AdminRef, [Archived Files].FileDescription, [File List].FileListName, [Archived Files].Address, [Archived Files].Town, [File List].Ref
FROM [File List] RIGHT JOIN (Property RIGHT JOIN (Admin RIGHT JOIN (Tenant RIGHT JOIN [Archived Files] ON Tenant.TenantID = [Archived Files].TenantID) ON Admin.AdminRefID = [Archived Files].AdminRefID) ON Property.PropertyID = [Archived Files].PropertyID) ON [File List].FirstListNameID = [Archived Files].FileListID
ORDER BY [Archived Files].FileNo;

[PhilOfWalton]

We're getting there very, very slowly.

Now all you need to tell me is what you want to see in your text box.

I think there are 2 options

1) "ABCDEF, HIJK, LMNOPQR" where ABCDEF represents TenantRef, HIJK represents AdminRef and LMNOPQR represents PropertyRef

2) If TenantRef is Null, Show AdminRef, but if both TenantRef and AdminRef are null, Show PropertyRef.

Am I close?

Phil

[glat]

Phil, yes you are correct: if the TenantRef is Null, show FileListID, if both are Null then show AdminRef. The PropertyRef is a text box on its own.

[PhilOfWalton]

We've got there !!!!!!!

So in your SQL, before the word "FROM" add

IIf(Not IsNull(TenantRef), TenantRef, IIf(Not IsNull(FileListID), FileListID, Nz(AdminRef)))) AS Ref

Phil