Sorting Dates

[Melinda]

I have an overtime db and I am having trouble sorting by date. We call out
employees to plow snow and per our union contract I need to sort in this
order. The seniority date is my fourth sort, I am sorting on expression for
outpost employees, by Class No., Sum of Hours Worked, and by Seniority Date.
For some reason if two employees have the same total hours worked, ie 90.7,
it reports an employee with a seniority date of 06-27-1994 before an employee
with a seniority date of 12-21-1992. The seniority date is a date/time
field. I can't move the seniority date up in the sorting order or it screws
everything up. I would appreciate any advise--

 

[John Spencer (MVP)]

As a guess, the total is slightly different than what you are seeing. One
Total is 90.70000001 and the other is 90.6999999.

You might try adding a calculated field and sorting by that field. Multiply
the time by 100 and then use the INT function to chop off any part past the
decimal.

Field: Int(TotalHoursWorked * 100)

Or use the round function to round the time to 2 places

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Melinda]

Would I make that change in the query or in the report, I haven't done
anything like this before, but it totally makes sense.

 

[John Spencer (MVP)]

Add the expression to the query and then sort by the expression in the report.

OR

In the sorting and grouping dialog of the report add the expression and sort
by the expression

Sort By
=Int(TotalHoursWorked * 100)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Melinda]

So if I am understanding this correctly I did this in the query, prior to the
calculated field my sum of Hours Worked was 93.90 and now it is 9385 Would
that be correct? and would I use this to sort on in the report?

 

[John Spencer (MVP)]

Yes.
Yes.

It wouldn't cost you much in time to try it. If it failed post back with the
error message or why the results were wrong. If it worked, post back and say
thank you.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Melinda]

Thank-you!!!! That looks as if it took care of my sorting problem. How
would I insert a decimal into the total hours field after I multiply it by a
100?

 

[John Spencer (MVP)]

You keep the original field and display it.
You *add* the calculated field and use it for sorting.

Or divide by 100 in the expression

(Int(TotalHoursWorked * 100)) /100

OR use the Round expression to accomplish this all in one step

Round(TotalHoursWorked,2)

If you use the either function, you can just use one field.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Melinda]

Thanks so much, John.....it works. Never gave it a thought that I could
have sorted on that field. Boy, I have alot to learn. Thanks so much for
all your help.