By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 897 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Add info from another field if present field is empty

P: 59
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
Expand|Select|Wrap|Line Numbers
  1. =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!
Jun 22 '17 #1

✓ answered by 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.

Expand|Select|Wrap|Line Numbers
  1.  IIf(IsNull([RefNo]), [AdminRef],[RefNo])
  2.  
Phil

Share this Question
Share on Google+
18 Replies


PhilOfWalton
Expert 100+
P: 1,430
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.

Expand|Select|Wrap|Line Numbers
  1.  IIf(IsNull([RefNo]), [AdminRef],[RefNo])
  2.  
Phil
Jun 22 '17 #2

NeoPa
Expert Mod 15k+
P: 31,186
Or, more simply, :
Expand|Select|Wrap|Line Numbers
  1. Nz([RefNo]), [AdminRef])
Jun 22 '17 #3

P: 59
Phil, can more than one field be added or is it limited to two?
Jun 23 '17 #4

PhilOfWalton
Expert 100+
P: 1,430
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
Jun 23 '17 #5

NeoPa
Expert Mod 15k+
P: 31,186
... 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 :
Expand|Select|Wrap|Line Numbers
  1. Nz([A],Nz([b],Nz([C],[D])))
Jun 24 '17 #6

P: 59
Phil it is in an unbound text box in the report. It is not calculated.
Jun 26 '17 #7

P: 59
Sorry NeoPa.

I tried your Nz option however all I kept getting was #Type in my text box.
Jun 26 '17 #8

PhilOfWalton
Expert 100+
P: 1,430
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
Jun 26 '17 #9

P: 59
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.
Jun 26 '17 #10

PhilOfWalton
Expert 100+
P: 1,430
OK, Glat

Can you post the SQL for the report's RecordSource

Phil
Jun 26 '17 #11

P: 59
Hi Phil,its
Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. ORDER BY [Archived Files].FileNo;
  4.  
Jun 26 '17 #12

PhilOfWalton
Expert 100+
P: 1,430
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
Jun 26 '17 #13

P: 59
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.
Jun 27 '17 #14

PhilOfWalton
Expert 100+
P: 1,430
We've got there !!!!!!!

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

Expand|Select|Wrap|Line Numbers
  1. IIf(Not IsNull(TenantRef), TenantRef, IIf(Not IsNull(FileListID), FileListID, Nz(AdminRef)))) AS Ref
  2.  
Phil
Jun 27 '17 #15

P: 59
Phil, sorry got caught up yesterday. Put in the SQL and receiving a message saying "Syntax error in FROM clause".
Jun 28 '17 #16

P: 59
I have however put the code in an unbound text box as
Expand|Select|Wrap|Line Numbers
  1. =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.
Jun 28 '17 #17

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. =Nz([TenantRef],Nz([FileListID],Nz([AdminRef],'')))
If at least one of them is always guaranteed to be non-null then even :
Expand|Select|Wrap|Line Numbers
  1. =Nz([TenantRef],Nz([FileListID],[AdminRef]))
Jun 28 '17 #18

P: 59
Neopa, somehow I did not receive your last reply. I tried your
Expand|Select|Wrap|Line Numbers
  1. =Nz([TenantRef],Nz([FileListID],Nz([AdminRef],'')))
and this one did work for me as well.

Thank you.
Jul 12 '17 #19

Post your reply

Sign in to post your reply or Sign up for a free account.