Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136

    Unanswered: Will a nested IIf statement work for my problem?

    I have a table called Reviews. I have a query based on this table. There are several fields I'm using parameters in to filter out certain records.

    I'm running in to a problem when I try to filter out certain records on 5 date fields.
    [1stReview] [2ndReview] [3rdReview] [4thReview] [DelinquentReview]

    All records in this query will always have a date in the first field, some records will have a date in the first field only, some only the first two fields, some only the first three fields, some only the first four fields, and some with all the fields. There will never be a second review without a first review, there will never be a third review without a second and first review . . . . .

    I only want to see the most current review date. If there are dates in the first three fields, I want to see the date in the third field only. If there are dates in all five fields, I want to see the date in the fifth field only.

    I've been trying to use nested IIf statements in the query grid with a new label called "LastReviewed" but I can't get it to work correctly. I can get it to display the [1stReview] if [2ndReview] is null and it will display the [2ndReview] if there is a date there instead of the [1stReview]. But when I keep adding IIf statements to filter the rest of the fields, I get lost.

    LastReviewed: IIf([DelinquentReview] Is Null And [4thReview] Is Null And [3rdReview] Is Null And [2ndReview] Is Null,[1stReview],[2ndReview])

    Is there a better way to do this?
    Thanks,
    Zenaida


    - If you've never made a mistake, you've never made anything.

  2. #2
    Join Date
    Jul 2005
    Posts
    39
    Maybe this will help?
    Code:
    SELECT tblReview.empNo, nz([noreview],nz([review4],nz([review3],nz([review2],[review1])))) AS lastReviewed
    FROM tblReview
    GROUP BY tblReview.empNo, nz([noreview],nz([review4],nz([review3],nz([review2],[review1]))));
    Would it not be easier if you had one date field and then your query would be on max(date)?

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    agree with taurus

    Have review table

    ID | ReviewStatus | Review Date | WhoID |


    Them you could query the table by
    Grouping the Whoid getting the Last Review date showing the Reveiwstatus
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    Thank you for both replies. And thanks for your suggestion. Before I got started on the Reviews table I tried to talk the user into having just one review date field with another field showing the review status but they want to be able to see all the dates the reviews were done.

    I used your code and it works great! Thanks again for the code and your suggestion.
    Thanks,
    Zenaida


    - If you've never made a mistake, you've never made anything.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •