Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Select date prior to Max date on total query (2003)

    Hi! I’m looking for a way to show a date that could be between the MIN and MAX date on a total query. I would like to use an IIf statement where if there is a MAX date then select the prior date to the MAX date. I tried using the Between...And function but it returns a number value.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select date prior to Max date on total query (2003)

    You can do this in two steps:

    1) Create a Top 2 query that returns the two highest dates

    - Create a query based on the table.
    - Add the date field to the query grid.
    - Specify Descending as sort order for this field.
    - Click in an empty part of the upper half of the query design window.
    - Activate the Properties window.
    - Enter 2 in the Top values property (you can't select it from the dropdown list but you can type it).
    - Save this query as qryTop2.

    2) Create a query based on the first one that selects the lowest of the two dates:

    - Create a query based on qryTop2.
    - Select View | Totals.
    - Add the date field to the query grid.
    - Set the Total option for this field to Min.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Select date prior to Max date on total query (2003)

    Sorry for not being more clear, the guidance you gave work captures what I am looking for; I forgot to tell say that I have a multitude of users ID with this data that I am trying to compress, for example:

    User ID:1 have 4 records in the table with starting date of a task 2/3/2007, 10/4/2008,11/23/2008 and 12/12/2008. I would like to have the same process that you explained (selecting the last two records) of all the records he might have in the table, and the same will go for every user.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select date prior to Max date on total query (2003)

    See ACC2000: How to Create a "Top N Values Per Group" Query (works for later versions too).

  5. #5
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Select date prior to Max date on total query (2003)

    Thank again Hans, always having the answer no matter the problem. That solved my problem.

Posting Permissions

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