Results 1 to 3 of 3

Thread: Sorting (XP-2)

  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting (XP-2)

    I have no idea why this is happening.


    I have a Query with the following Calculation Field
    Formated:General Number

    Running RPN: MinVal(Primary RPN,Final RPN)
    - please note I removed the brackets from around the fields in the ()brackets as they would not show


    MinVal Module
    Function MinVal(ParamArray SomeValues()) As Variant
    Dim i As Integer
    MinVal = Null
    For i = LBound(SomeValues) To UBound(SomeValues)
    If Not IsNull(SomeValues(i)) Then
    If IsNull(MinVal) Then
    MinVal = SomeValues(i)
    ElseIf SomeValues(i) < MinVal Then
    MinVal = SomeValues(i)
    End If
    End If
    Next i
    End Function

    Not sorted it seems to work fine
    I try to set the sort ascending or descending in the query and suddenly it asks for a parameter value for [Primary RPN] which it did not before.
    I switch to datasheet view (not sorting the field in design view) and it fills in all the info, so I tried sorting the field and it sorts

    11
    108
    126
    21
    208
    3
    31
    325

    Everywhere I look I have it set as a General Number so I do not know why it is sorting this like it was a text field...

    I would rather not change the module as it is used for other things in the database if it could be helped but if it need to change so be it.... thanks everybody for all your help....

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

    Re: Sorting (XP-2)

    The MinVal function deliberately doesn't specify its return type, since it can be used for numbers, currency, dates or strings. As a consequence, Access treats the result as a string. To get around this, use

    Running RPN: Val(MinVal(Primary RPN,Final RPN))

    Val converts the result to a numeric value.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Kitchener, Ontario, Canada
    Posts
    228
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting (XP-2)

    I was trying the Val ( but in the wrong places) again.. thanks for all the help Hans...

Posting Permissions

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