More Sorting "coding" help needed

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Access 2007

Based upon 3 fields. Caller (text field), Status (Combo), Inactive (Y/N)

I want to first sort alphabetically by caller
Then
Status = Ineligible
Then
Inactive


This is the attempt I made - which did not work.

Code: 
Private Sub cmdSortCaller_Click()
Me.OrderBy = "Caller"
Me.OrderBy = "Status = Ineligible" & " Desc"
Me.OrderBy = "Inactive" & " Desc"
Me.OrderByOn = True
End Sub

Any help will be appreciated

Dave
 
You need to set the form's OrderBy property to a string that looks like the
ORDER BY clause of a query. You can therefore mock up a query that sorts the
data the way you want, switch it to SQL View, and you have an example of the
string you need to create.

For the example you gave, it will be something like this:

Private Sub cmdSortCaller_Click()
Dim strSort As String
If Me.Dirty Then Me.Dirty = False 'Save first
strSort = "[Caller], ([Status]=""Ineligible""), [Inactive] DESC"
Me.OrderBy = strSort
Me.OrderByOn = True
End Sub

There will be 3 groups for the middle field:
- True (if Status is that word)
- False (if it's something else)
- Null (if there's nothing in the field.)

Simiarly, I assumed you wanted the active records first, so the 3rd field
has a descending sort.
 
Getting a "Data type mismatch" on the status field which I will have to
figure out but other wise this was a big help.

Thanks

dave

Allen Browne said:
You need to set the form's OrderBy property to a string that looks like
the ORDER BY clause of a query. You can therefore mock up a query that
sorts the data the way you want, switch it to SQL View, and you have an
example of the string you need to create.

For the example you gave, it will be something like this:

Private Sub cmdSortCaller_Click()
Dim strSort As String
If Me.Dirty Then Me.Dirty = False 'Save first
strSort = "[Caller], ([Status]=""Ineligible""), [Inactive] DESC"
Me.OrderBy = strSort
Me.OrderByOn = True
End Sub

There will be 3 groups for the middle field:
- True (if Status is that word)
- False (if it's something else)
- Null (if there's nothing in the field.)

Simiarly, I assumed you wanted the active records first, so the 3rd field
has a descending sort.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Dave said:
Access 2007

Based upon 3 fields. Caller (text field), Status (Combo), Inactive (Y/N)

I want to first sort alphabetically by caller
Then
Status = Ineligible
Then
Inactive

This is the attempt I made - which did not work.

Code: 
Private Sub cmdSortCaller_Click()
Me.OrderBy = "Caller"
Me.OrderBy = "Status = Ineligible" & " Desc"
Me.OrderBy = "Inactive" & " Desc"
Me.OrderByOn = True
End Sub
Click to expand...
Click to expand...
 
Let me guess. Your Status field was defined as a lookup field. That means
that while you may see "Ineligible", "Inactive" and so on in the field,
what's actually stored is the key value from the related lookup table. This
is yet one of the many reasons why most of us avoid lookup fields. (See
http://www.mvps.org/access/lookupfields.htm at "The Access Web" for some
other reasons why.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dave said:
Getting a "Data type mismatch" on the status field which I will have to
figure out but other wise this was a big help.

Thanks

dave

Allen Browne said:
You need to set the form's OrderBy property to a string that looks like
the ORDER BY clause of a query. You can therefore mock up a query that
sorts the data the way you want, switch it to SQL View, and you have an
example of the string you need to create.

For the example you gave, it will be something like this:

Private Sub cmdSortCaller_Click()
Dim strSort As String
If Me.Dirty Then Me.Dirty = False 'Save first
strSort = "[Caller], ([Status]=""Ineligible""), [Inactive] DESC"
Me.OrderBy = strSort
Me.OrderByOn = True
End Sub

There will be 3 groups for the middle field:
- True (if Status is that word)
- False (if it's something else)
- Null (if there's nothing in the field.)

Simiarly, I assumed you wanted the active records first, so the 3rd field
has a descending sort.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Dave said:
Access 2007

Based upon 3 fields. Caller (text field), Status (Combo), Inactive (Y/N)

I want to first sort alphabetically by caller
Then
Status = Ineligible
Then
Inactive

This is the attempt I made - which did not work.

Code: 
Private Sub cmdSortCaller_Click()
Me.OrderBy = "Caller"
Me.OrderBy = "Status = Ineligible" & " Desc"
Me.OrderBy = "Inactive" & " Desc"
Me.OrderByOn = True
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top