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

[glat]

Phil, sorry got caught up yesterday. Put in the SQL and receiving a message saying "Syntax error in FROM clause".

[glat]

I have however put the code in an unbound text box as

=IIf(Not IsNull([TenantRef]),[TenantRef],IIf(Not IsNull([FileListID]),[FileListID],Nz([AdminRef])))

and it works perfectly.

I have also used same code in another unbound text box for other fields as well.

Thank you for all your help.

[NeoPa]

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

I must have missed this earlier.

I'm confident that using Nz() should both work and give you what you asked for. Can you post your exact attempt at following the suggestion. I suspect we may be able to help you resolve it. One thing to bear in mind using Nz() is that the type of value to use if Null is important in most cases.

Now I've caught up with the rest of the posts I can see you may not be interested in following up any longer. That's fine, but if you want to see it done with just Nz(), which would be quite sensible, then feel free to continue on.

I would guess you want something like :

=Nz([TenantRef],Nz([FileListID],Nz([AdminRef],'')))

If at least one of them is always guaranteed to be non-null then even :

=Nz([TenantRef],Nz([FileListID],[AdminRef]))

[glat]

Neopa, somehow I did not receive your last reply. I tried your

=Nz([TenantRef],Nz([FileListID],Nz([AdminRef],'')))

and this one did work for me as well.

Thank you.