Query not sorting calculated fields correctly

[brianj]

Hello,

I am new to MS Access dev and could use some help with this problem.

I'm creating a database for my son's school team to track runners' times'. Fields in the RunnerDataTable are: Time 1, Time 2, Time 3, Time 4.
All are Number, Field Size=Double, Decimal Place=3, Required=No, Indexed=No

I also use the following module to determine the lowest and highest times in each record for that table.

Function Minimum(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Variant

' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)

' Cycle through each value from the row to find the smallest.
For I = 0 To UBound(FieldArray)
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
Next I

' Return the minimum value found.
Minimum = currentVal

End Function

Function Maximum(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Variant

' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)

' Cycle through each value from the row to find the largest.

For I = 0 To UBound(FieldArray)
If FieldArray(I) > currentVal Then
currentVal = FieldArray(I)
End If
Next I

' Return the maximum value found.
Maximum = currentVal

End Function


In a Query, I use the following to extract the Minimum and Maximum times from the table for each record.

Slowest Time: Maximum([Time 1],[Time 2],[Time 3],[Time 4])
Fastest Time: Minimum([Time 1],[Time 2],[Time 3],[Time 4])

This works 100% except when trying to sort by ascending or descending in the queries. (I use separate query for each Slowest and Fastest Time sorting.)

The results are for example:
Sorted by Ascending for Fastest Time

10.32
11.456
12.08
20.178
4.23
8.56
9.897

It appears that the program is looking at the first digit and sorting by that number and not the entire number.
Any help is appreciated. Thank you in advance.

 

[GPGeorge]

Your sort is based on the fact that the values you calculate are converted to strings, not numbers.

 

[The_Doc_Man]

It would appears that you are sorting as though the items were in TEXT mode. That IS a text sort order. You have to figure out why, but it may be because intrinsic functions return variants which are usually TEXT until they have to be something else.

 

[brianj]

Your sort is based on the fact that the values you calculate are converted to strings, not numbers.

 

[brianj]

Thanks GpGeorge. Is there a way to convert the strings to numbers? Not a programmer here, just a coach. Thanks again.

 

[The_Doc_Man]

Your problem is that your "Minimum" function has no explicit type in its declaration, which means it becomes a variant. Variants are returned as strings and remain that way if they don't have to be changed. If you have numeric times in the actual record (i.e. datatype Single or Double), you could make a SELECT clause in which you include an "ORDER BY" clause on the timing field. I suggest you do some reading on ORDER BY clauses.

ORDER BY Clause - Microsoft Support

The ORDER BY clause in Access sorts a query's resulting records on a specified field or fields in ascending or descending order.

support.microsoft.com

NOTE that if you want the report to be sorted, you would not do it via the query but rather via the report builder's GROUPING AND ORDERING options. Access reports are a bit... independent sometimes.

 

[GPGeorge]

Thanks GpGeorge. Is there a way to convert the strings to numbers? Not a programmer here, just a coach. Thanks again.

See theDocMan's response.

 

[CJ_London]

Slowest Time: Maximum([Time 1],[Time 2],[Time 3],[Time 4])

Your two functions are designed to sort anything- numbers, text, dates

So try converting to the type you need - which looks like it needs to be a double

Slowest Time: cDbl(Maximum([Time 1],[Time 2],[Time 3],[Time 4]))

 

[brianj]

CJ_London.......That did it. Thanks to the group for all the responses. Learned a little bit more today from The Doc Man and GP George.

 

[Eugene-LS]

It appears that the program is looking at the first digit and sorting by that number and not the entire number.

Try to convert result to Date format :

Spoiler: Functions text

Function Minimum(ParamArray FieldArray() As Variant) As Date
' Declare the two local variables.
Dim iVal As Integer
Dim dCurrentDateTime As Variant

' Set the variable dCurrentDateTime equal to the array of values.
    dCurrentDateTime = FieldArray(0)

' Cycle through each value from the row to find the smallest.
    For iVal = 0 To UBound(FieldArray)
        If FieldArray(iVal) < dCurrentDateTime Then
            dCurrentDateTime = FieldArray(iVal)
        End If
    Next iVal

' Return the minimum value found.
    Minimum = dCurrentDateTime

End Function

Function Maximum(ParamArray FieldArray() As Variant) As Date
' Declare the two local variables.
Dim iVal As Integer
Dim dCurrentDateTime As Variant

' Set the variable dCurrentDateTime equal to the array of values.
    dCurrentDateTime = FieldArray(0)

' Cycle through each value from the row to find the largest.
    
    For iVal = 0 To UBound(FieldArray)
        If FieldArray(iVal) > dCurrentDateTime Then
            dCurrentDateTime = FieldArray(iVal)
        End If
    Next iVal

' Return the maximum value found.
    Maximum = dCurrentDateTime

End Function