Sorting 3 fields

[jjb373]

I have 3 different fields in a report with a time value in this format: _d _hr _min

Is there any way to sort all 3. Currently my report sorts one field then the next then the 3rd. I want to sort so that the 3 fields are not divided up. Is this clear?

field 1 field 2 field 3
1d 2h 0min
1d 1h 15min
0d 23hr 2min
0d 12hr 0min


Also, my report right now looks like this:

9d 5hr 20min
6d 0hr 15min
1d 2hr 0min
0d 13hr 45min
11d 2hr 25min

I want the 11d 2hr 25min line on top. How do I do that?

Thanks in advance

 

[ZmrAbdulla]

Did you try doing it with "Grouping & Sorting" fron the toolbar?

________________________________________________________
Zameer Abdulla
Help to find Missing people
Even a thief takes ten years to learn his trade.

 

[HandsOnAccess]

It is slightly different from using Grouping and Sorting.

Here is the concept as to how you must implement it

First Change the recordsource of the report to a query.
In the query retain the original recordsource but add 2 new expression.

The two expressions are
Val(Mid([Field1] & [Field2] & [Field3],1,2)) AS SortField
This expression helps to sort in decending order.
It extracts only the hour value from the time.

[Field1] & [Field2] & [Field3] AS MergedField
This expression merges data from 3 fields to a single field and helps is overall sorting.

SELECT *, Val(Mid([Field1] & [Field2] & [Field3],1,2)) AS SortField, [Field1] & [Field2] & [Field3] AS MergedField
FROM <<Table Name>>
ORDER BY Val(Mid([Field1] & [Field2] & [Field3],1,2)) DESC , [Field1] & [Field2] & [Field3] DESC;

Make Sure Field1, Field2 and Field3 do not contain Null Values.

Remove All Group and Sorting in your report.

Preview the report and let me know how it goes...

Hope this helps you...

Regards,

 

[dhookom]

jjb373
Why doesn't your sample data at the top match the "right now looks like this"? Why would 11d 2hr 25min sort to the top?

What is your actual requirement for sorting? Does each record only have one of the fields populated with a value? Do you understand that having multiple fields like this violates normalization standards?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jjb373]

Duane....the sample data were just examples i used to give a quick mental picture.

The right now looks like: is asking a different question.