We help IT Professionals succeed at work.

Not sorting Ascending

Frank Freese
Frank Freese asked
on
Folks,
In my Access 2016 form I have various fields that when you click on their label it will sort the table Descending. For example, if I clicked on the label Address for the Address control then then table is sorted Descending. What I haven't figured out is if I wanted to then click on the same label it would sort Ascending.

Private Sub Address_Label_Click()
        Me.OrderBy = "Address"
        If Me.OrderBy = "Address" Then
           Me.OrderBy = "Address DESC"
        Else
           Me.OrderBy = "Address"
        End If
End Sub

Open in new window

Comment
Watch Question

View Solution Only

Anders Ebro (Microsoft MVP)Microsoft Developer

Commented:
You were close, you just need to remove the first line of your code:
Private Sub Address_Label_Click()
        If Me.OrderBy = "Address" Then
           Me.OrderBy = "Address DESC"
        Else
           Me.OrderBy = "Address"
        End If
End Sub

Open in new window

Software & Systems Engineer
Distinguished Expert 2019
Commented:
Check this code (tested and working)
Private Sub Address_Label_Click()
Dim tmpOrder As String
If Len(Me.OrderBy) > 0 Then
If InStr(Me.OrderBy, "DESC") Then
    tmpOrder = Me.OrderBy
    tmpOrder = Replace(tmpOrder, "DESC", "")
    Me.OrderBy = tmpOrder
Else
Me.OrderBy = "Address DESC"

End If
Else
    Me.OrderBy = "Address"
End If
Me.OrderByOn = True
End Sub

Open in new window

Frank Freese

Author

Commented:
Thanks to the two of you. John your code worded perfectly!