Sort - different order?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to sort the records in a report in a nonconventional way... I want to
sort all records that have been assigned a value of 14 first, then 18, then
13 down to 1, and then 16-17. How do I do this? I set up a different report
for each of these scenarios and then added them as sub-reports to a report
but the formatting is kind of strange when I do this. Any ideas?
 
Heather,
You could try a "sorting table". Create a seprate table tblSortTable
with 2 fields... Number and SortOrder. Make Number the same type as the
numbers you referred to in your post (14, 18, 13-1.. etc)
Then enter data into it like this...
Number SortOrder
14 1
18 2
13 3
12 4
11 5
.... ... (for all 13)
16 16
17 17

You should be able to link this table in your query to your data file,
and sort by Number Ascending.
 
I want to sort the records in a report in a nonconventional way... I want to
sort all records that have been assigned a value of 14 first, then 18, then
13 down to 1, and then 16-17. How do I do this? I set up a different report
for each of these scenarios and then added them as sub-reports to a report
but the formatting is kind of strange when I do this. Any ideas?
Click to expand...

Base the report upon a query.

Copy this to a new Module:
Function GetOrder(FieldIn) as Integer
Dim intX as Integer
Select Case FieldIn
Case is = 14
intX = 1
Case is = 18
intX = 2
Case is = 13
intX = 3
' etc...
End Select
GetOrder = intX
End Function

Fill in the full sort order following the above example.

Then add a new column to the query.

SortThis:GetOrder(AssignedField])

Sort the report using this new SortThis field.
 
Back
Top