Sorting Using OrderBy (1 Viewer)

Can anyone help me please.
This was my code that basically sorted records displayed in a form by assending order then by descending order if you clicked the header MasterLbl.


Private Sub MasterLbl_Click()
If Me.OrderBy = "[MastRef]" Then
Me.OrderBy = "[MastRef] DESC"
Else
Me.OrderBy = "[MastRef]"
Me.OrderByOn = True
End If
End Sub

This worked a piece of cake, but what I wanted to do was two other data fields to it like thus:

Private Sub MasterLbl_Click()
If Me.OrderBy = "[MastRef],[SubRef],[DiffRef]" Then
Me.OrderBy = "[MastRef] DESC,[SubRef] DESC,[DiffRef] DESC"
Else
Me.OrderBy = "[MastRef],[SubRef],[DiffRef]"
Me.OrderByOn = True
End If
End Sub

But this does not work, how can I get it to sort so that all 3 columns are in order ascending and decsending with each click of the label. Could I do it with an IF statement if I assigned a value to a variable, how would I do that.
Any help would be great.
Thanx

I think thyat the ORDER BY should be
and
instead of
and

Look at a query and ordering in the QBE window and then look at its SQL.

Almost worked, I used this as you suggested:

Private Sub MasterLbl_Click()
If Me.OrderBy = "[MastRef] AND [SubRef] AND [DiffRef]" Then
Me.OrderBy = "[MastRef] DESC AND [SubRef] DESC AND [DiffRef] DESC"
Else
Me.OrderBy = "[MastRef] AND [SubRef] AND [DiffRef]"
Me.OrderByOn = True
End If
End Sub

But it just puts it in order of the primary key column which incidently is non of the above. Clicking it does toggle between Ascending and decending though so nearly there.
Thanx

This is what you should do.

Write this query explicitly using the query design grid. Include ASCENDING or DESCENDING in the appropriate spots in the grid.

Now switch to SQL view of that query. Find the ORDER BY clause. Starting with the first element after the ORDER BY, load that to your ORDER BY property on the form.

Now do the same but flip-flop ASCENDING and DESCENDING.

Now look at SQL view again. Whatever you see is the correct syntax for the order-by having the opposite order.

I have tried this but the SQL is different to the VBA code. The differences are subtle but non the less they are different.
Cheers

Yes!
Sorted it!!! So simple.

Ditch the brackets...

Private Sub MasterLbl_Click()
If Me.OrderBy = "MastRef, SubRef, DiffRef" Then
Me.OrderBy = "MastRef DESC, SubRef DESC, DiffRef DESC"
Else
Me.OrderBy = "MastRef, SubRef, DiffRef"
Me.OrderByOn = True
End If
End Sub

Why this would make the difference I do not know.
Cheers for replies.

Why this would make the difference I do not know.

Click to expand...

Well, it IS a Bill Gates product.