Non-Standard Grouping/prdering

[ShaneBrennan]

Okay I'm using Access 97 and say I have the following data in tblData:

UnitID AssCrit Description
------ ------- -----------
Unit1 A01 How to tie a knot
Unit1 A02 Doing the do
Unit1 C01 How to buy Fish
Unit1 C02 Learning to read the time
Unit1 E01 Using a calculator
Unit1 E02 Filleting Fish
Unit1 E03 Carbon-dating the worms

Okay the information is silly, but I can use it to base my question upon.

Okay, say I needed to produce a report with the above information, but the order of AssCrit needs to be C's, A's then E's. How could I go about achieving this.

I could set the ordering to Ascending - but that would be A,C,E - or descending - E,C and A - but what if I want to specify a non-standard group, is there a way of forcing Access to do this?

Shane
PS. I know I need professional help


Shane Brennan
Shane.Brennan@tcat.ac.uk

 

[MichaelRed]

?Professional Help? YES. But then I also need help - professional or ortherwise.

IF (and I STRESS the IF), the ordering will be consistient (e.g. your professional help will not change their mind about the 'C', 'E', 'A' and decide to make it 'A', 'E', then 'C' ... or you are going to add a LOT of itme codes, you can do it with a simple calculated field expression:

Order: IIF(Left(AssCrit, 1) = "A", 3, IIF(Left(AssCrit, 1) = "C", 1, IIF(Left(AssCrit, 1) = "E", 2, 0)

Then, order the recordset by the 'Order' column.

If you have a few additional AssCrit codes, it would be easier to put the whole conditional thing in a module/procedure.


MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[ShaneBrennan]

Thanks Michael I'll give it a go.

Shane
Shane Brennan
Shane.Brennan@tcat.ac.uk

 

[MichaelRed]

Public Function basCustSort(strIn As String) As Integer

    Dim ChrOrder(26) As String
    Dim Idx As Integer

    'Usage:
    '? bascustsort("A01")
    '2
    
    'Or (from a Query)
    'Order: basCustSort([AssCrit])

    'Function to provide a simple first character collating sequence
    'Order for a field which (presumably) has a character as the first
    'byte in the string.  Any Character not in the ChrOrder array will
    'return a Zero for the order, placing all of htese Items First in
    'the orderinf

    ChrOrder(1) = "C"
    ChrOrder(2) = "A"
    ChrOrder(3) = "E"
    ChrOrder(4) = ""
    ChrOrder(5) = ""
    ChrOrder(6) = ""
    ChrOrder(7) = ""
    ChrOrder(8) = ""
    ChrOrder(9) = ""
    ChrOrder(10) = ""
    ChrOrder(11) = ""
    ChrOrder(12) = ""
    ChrOrder(13) = ""
    ChrOrder(14) = ""
    ChrOrder(15) = ""
    ChrOrder(16) = ""
    ChrOrder(17) = ""
    ChrOrder(18) = ""
    ChrOrder(19) = ""
    ChrOrder(20) = ""
    ChrOrder(21) = ""
    ChrOrder(22) = ""
    ChrOrder(23) = ""
    ChrOrder(24) = ""
    ChrOrder(25) = ""
    ChrOrder(26) = ""
    
    MyChr = UCase(left(strIn, 1))

    For Idx = 1 To UBound(ChrOrder)
        If (MyChr = ChrOrder(Idx)) Then
            basCustSort = Idx
            Exit For
        End If
    Next Idx

End Function

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over