Pull most current

Screen ShotI have a form that has 3 author fields.  I want to create a report that will pull the most current author.  So if there is a plan author listed in the first field, but not the second.  I want it to list the first field.  If there is an author listed in the first and second field I want it to pull the second.  And if there is an author listed in the first, second and third field I want it to pull the third field so that it pulls the most current author
shannonh74Asked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
You need to add another NZ() function call


LatestAuthor: NZ([Author4], NZ([Author3], NZ([Author2], [Author1])))

The NZ( ) function will return the 1st argument if it is not null, but if it is null, then it returns the 2nd argument.  By nesting several of these, you can test for Author4, then 3, 2, and 1
0
 
mbizupCommented:
Youd use a query like this (modify with your own field names)

SELECT iif( "" & [Field1] <> "" AND "" & [Field2] <> "" AND "" & [Field3] <> "", [Field3], IIF([Field2] & "" <> "", [Field2], [Field1]))
FROM YourTable

That said, you should really be using a seperate table to handle these authors, with a one-to many relationship with the main "support plan" record -- displaying the data in subforms or subreports (which you can easily set up queries to display the needed data)

http://office.microsoft.com/en-us/access-help/create-a-form-that-contains-a-subform-a-one-to-many-form-HA010098674.aspx
0
 
shannonh74Author Commented:
I get an error saying the synthax of the subquery in the expression is incorrect.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mbizupCommented:
Did you read the article I linked to?

That is really the best design / approach to this.
0
 
shannonh74Author Commented:
Yes, I understand that.  And had I know prior to creating the database that there were going to be multiple authors I would have done this.  However, right now, I am not going to change the database.  I need a quick fix for just pulling the most current author.
0
 
Dale FyeCommented:
How about:

LatestAuthor: NZ([Author3], NZ([Author2], [Author]))
0
 
mbizupCommented:
The IIF statement I posted earlier is syntactically correct, and does what you are looking for  - I just tested it.

Post your own SQL, showing how you implemented it.
0
 
shannonh74Author Commented:
fyed, that worked great!!!  I have played with it and tried to add another field because I have actually 4 author fields and I am not having any luck adding it.  Can you tell me where I would add the 4th field.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.