Results 1 to 2 of 2
  1. #1
    ChrisP is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Feb 2015
    Posts
    1

    How to Find 2nd Highest / 2nd Lowest Value Across Fields of Record

    I found this guide for getting the Min and Max

    http://support.microsoft.com/kb/209857

    Only problem is my VBA functions require the 2nd highest / 2nd lowest of (3) fields

    Min and Max will get 2 of those but how do I call and declare the middle value. ( The 2nd highest / of 2nd lowest)



    Thanks!


    \to Find Minimum or Maximum Value Across Fields of Record

    How to Find Minimum or Maximum Value Across Fields of Record


  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if you're going to use that minimum and maximum function and you only have 3 values to compare then you could use a formula like:

    So let's say you have a table with three fields
    FieldA, FieldB and FieldC

    You can get the min of the three values with: minval: minimum([fielda],[fieldb],[fieldc])
    You can get the maximum of the three values with: maxval: maximum([fielda],[fieldb],[fieldc])
    Assuming you only have three values you can get the middle value with: midvalue: IIf([fielda]<>[minval] And [fielda]<>[maxval],[fielda],IIf([fieldb]<>[minval] And [fieldb]<>[maxval],[fieldb],IIf([fieldc]<>[minval] And [fieldc]<>[maxval],[fieldc],Null)))


    Or you can go looking for a median function:

    https://msdn.microsoft.com/en-us/library/dd789431.aspx

    You would have to adapt this to work with an array similar to the first two functions.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Find a record with the highest value
    By Lynghaug in forum Queries
    Replies: 3
    Last Post: 06-15-2012, 06:02 AM
  2. Find highest 4 hours out of 24
    By nvrwrkn in forum Queries
    Replies: 6
    Last Post: 06-15-2012, 05:22 AM
  3. Using expression builder to find lowest date
    By MMcKenna in forum Queries
    Replies: 1
    Last Post: 03-20-2012, 02:28 PM
  4. Find 2nd or 3rd highest record with a query
    By K Roger in forum Queries
    Replies: 1
    Last Post: 12-19-2011, 11:27 PM
  5. Query to find lowest value
    By AccessNubie in forum Access
    Replies: 3
    Last Post: 11-30-2009, 07:54 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums