Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Next record in a sorted or (Rank ordered) list (2003)

    I have been using queries to pool data together and extract specific values.

    Now, I need to get the next value of a rank ordered or sorted list. If I have a sorted list of values (or records), such as, 1.1, 2.8, 9.5, 10.8, 20.1 and I have the value of 9.5; I need to get 10.8. I would like to stay with queries if possible. I was also thinking of using a dynamic recordset with a bookmark in VB and just incrementing and looking at the the next record.

    any approaches?

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

    Re: Next record in a sorted or (Rank ordered) list (2003)

    In a query, you can use a calculated column like this:

    NextValue: DMin("NameOfField", "NameOfTableOrQuery", "[NameOfField] > " & [NameOfField])

    Also see ACC2000: Referring to a Field in the Previous Record or Next Record (applies to later versions of Access too)

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Next record in a sorted or (Rank ordered) list (2003)

    I'll give the query calc a whirl and let you know how it works out. Seems like it will fill the bill.

    Thanks for the MS link. I spent a couple of hours this morning doing next record and next row searches on MS and Google and never saw this particular article. Amazes me what you don't find when searching.

Posting Permissions

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