[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

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
0
shannonh74
Asked:
shannonh74
  • 3
  • 3
  • 2
1 Solution
 
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
 
mbizupCommented:
Did you read the article I linked to?

That is really the best design / approach to this.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
Dale FyeCommented:
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now