Access VBA - OrderBy with multiple field

Question

I wonder how I should do this. Actually, I have some subform and, by clicking on the title, I want the recordset of this subform to orderby. This is an example of what I do:

Private Sub sigle_cours_Label_Click()
    If (Me.OrderBy = "COU.sigle_cours") Then
        Me.OrderBy = "COU.sigle_cours DESC"
    Else
        Me.OrderBy = "COU.sigle_cours"
    End If
    Me.OrderByOn = True
End Sub

My problem is this one : I want to OrderBy with sigle_cours AND num_cours but it don't work. I try this but there's no way to sort by DESC :

Private Sub sigle_cours_Label_Click()
    If (Me.OrderBy = "COU.sigle_cours,COU.num_cours") Then
        Me.OrderBy = "COU.sigle_cours DESC,COU.num_cours DESC"
    Else
        Me.OrderBy = "COU.sigle_cours,COU.num_cours"
    End If
    Me.OrderByOn = True
End Sub

How should I do this?

Solution

When you assign a string value to the OrderBy property, Access may transform it ... so won't exactly store what you expect. In your case, I suspect Access adds a space after the comma, so if you include this in your Form's code ...

Me.OrderBy = "COU.sigle_cours,COU.num_cours"
Debug.Print "Me.OrderBy='" & Me.OrderBy & "'"

You might see this in the Immediate Window ...

Me.OrderBy = 'COU.sigle_cours, COU.num_cours'

Actually I'm not positive that is the explanation for your problem. Nevertheless I suspect you're more likely to find joy by pattern-matching the current OrderBy value instead of testing for an exact match to a fixed string. Try it this way:

Private Sub sigle_cours_Label_Click()
    If (Me.OrderBy Like "*DESC*") Then
        Me.OrderBy = "COU.sigle_cours, COU.num_cours"
    Else
        Me.OrderBy = "COU.sigle_cours DESC, COU.num_cours DESC"
    End If
    Me.OrderByOn = True
End Sub

Comments

[David-W-Fenton]

Access can do very weird things to the form's ORDER BY property. I have an app where it inserts three or four spaces between the fields of the ORDER BY clause. The easiest thing to do because of that is to Replace() all the spaces with nothing and then test against that.

[Patrick Pruneau]

You're the man!! After printing Me.OrderBy, I notice like you said that Access add a space after the comma. After this, it was a piece of cake to repair the code. Thanks!