strange results using orderby

[toast]

I am having some strange results using orderby on a form.

The set up is a simple combo box of column headings, with some vba to order the form according to the appropriate heading selected.

This works fine for the fields with names, numbers and dates. But I am getting some strange results when ordering by a field populated by the results of a function; it reorders the form without any error messages, but the results aren't in any consistent order (e.g. 2.5, 2.2, 3.8...)

The field in question is populated using a function which calls a SQL statement totaling a time field. The original field is in hh:nn format. This field is multiplied by 24 and summed in the SQL statement so that the function returns the total in decimal format. The actual figures returned by the function are correct, but the orderby is wrong.

The other strange thing (perhaps unrelated) is that occasionally when a selection is made in the combo box a message pops up asking for the parameter value. If nothing is entered and OK clicked, and then the same selection is made everything behaves as normal. This again is inconsistent about when it happens.

I've tried decompiling but it made no difference.

Any thoughts about why this might be happening?

The relevant bit of VBA for orderby:

Private Sub SortCombo_AfterUpdate()

Me.OrderbyOn = true

Select Case Me.SortCombo

Case 6
Me.OrderBy = "[shifttotal],[Fullname]"
...

End select

End Sub

The control source for [shifttotal] =GetShiftTime()

(error handling removed for posting)

Private Function GetShiftTime()

Dim rst as Recordset
Dim strSQL as String
Dim currentID as String

currentID = Me![EmployeeID]

strSQL = "SELECT Employees.EmployeeID, Sum(Deliveries.DeliveryTime)*24 As TotalTime FROM (Employees INNER JOIN Shifts ON (Employees.EmployeeID = Shifts.Loader) OR (Employees.EmployeeID = Shifts.Driver)) INNER JOIN Deliveries ON Shifts.ShiftID = Deliveries.ShiftID GROUP BY Employees.EmployeeID HAVING (((Employees.EmployeeID)=" & currentID & "));"

Set rst = CurrentDb.OpenRecordset(strSQL)

If rst.BOF And rst.EOF Then
GetShiftTime = ""
Else
GetShiftTime = rst(1)
End If

rst.Close
Set rst = Nothing
End Function

 

[Galaxiom]

Summing the delivery times and then multiplying them by 24 is not a valid operation to return the total of the hours. Time is a moment in time, not an expression of an elapsed time period.

If you show the results with a date included you will find that they are indeed in order. It is just that some of the times are on subsequent days.

 

[toast]

Thanks for your reply.

I thought about what you said, and created a calculated field in the table that converted the time difference to decimal:

Hour([ShiftDuration])+(Minute([ShiftDuration])/60)

I then ran the SQL based upon that decimal figure. The result was exactly the same.

So then I ran the SQL statement directly as follows:

SELECT Employees.EmployeeID, Sum(Deliveries.DecimalDeliveryTime) As TotalTime
FROM (Employees INNER JOIN Shifts ON (Employees.EmployeeID = Shifts.Loader) OR (Employees.EmployeeID = Shifts.Driver))
INNER JOIN Deliveries ON Shifts.ShiftID = Deliveries.ShiftID
GROUP BY Employees.EmployeeID
[b]ORDER BY Sum(Deliveries.DeliveryTime)[/b];

And this ordered them correctly.

So the problem appears to be with the form, rather than the data.

Is it possible that access is getting ahead of itself, and processing the orderby before the function has returned its value for each row?