Custom sort

[Jerome]

Hello,

I use this code to populate data from a table into a listbox.

Public Sub PullData()

'Purpose: to refresh the contents of the list box based on the text entered into the TxtCriteria control

On Error Resume Next
Dim StrSql              As String 'SQL statement for the record source

sText = Trim(Me.TextSearchString.Text)

StrSql = "SELECT * FROM TblChemicals "

'Is there any text to test?
    'If Not sText = "" And IsDelOrBack = False Then
            
            StrSql = StrSql & "WHERE " & Me.ComboCat.Column(1) & " Like '" & "*" & sText & "*" & "*' ORDER BY " & Me.ComboFiltered.Column(1) & ";"
                         
            'Refresh the rowsource with the new SQL
            Me.ListTest.RowSource = StrSql
                
    'End If
    'Requery the list box to show results
    Me.ListTest.Requery

End Sub

The problem is that I have a column containing the following data:

E 1, E 2, E 10, A 1, A 3, A 10 ect.....

When I put my sort order on this column I would like to see:

A 1, A 3, A 10, E 1, E 2, E 10 ect...

But I see:

A 1, A 10, A 3, E 1, E 10, E 2 ect...

How can I modify the sort order in order to make this work?

Thanks in advance.

 

[DCrake]

What Access is doing is correct. To fix this add another column to your query named Whatever and use the following

Whatever:Replace(WhateverField," ","0")

What you are doing is replacing all the empty spaces between the letter and digit(s) with leading zeros, so A 1 becomes A01, A 10 becomes "A010" and sort on this field.

David

 

[Jerome]

Ok,

I've did a quick test,

In the table I deleted all the spaces.

E1, E2, E10, A1, A3, A10.

When I apply the code as I described above it still gives:

A1, A10, A3, E1, E10, E2 ect....

So I think you solutions will not work.

 

[Rabbie]

This is because you are doing a text sort. E10 sorts before E2. Try splitting your sort field into two - a text part for the letters and a numeric part for the numbers. This should let you sort in the order you want.

 

[Jerome]

Ok thanks.

Well this should be no problem, but I would like to show the data in one column in the list box. Is there a way to join the two separate fields together in one column in the listbox?

Thanks.

 

[Alisa]

Yes, just put the recordsource of your list box as field1 & field2

 

[David R]

Yes, just put the recordsource of your list box as field1 & field2

If I remember right, this should be

=field1 & field2

And it's the Row Source, not the Control Source (calculated fields should be unbound).

 

[Atomic Shrimp]

Create two expression fields in your query:
Expr1: Left([YourColumnName],1)
Expr2: Val(Mid([YourColumnName],2))

Sort by both of these expressions (Expr1 first) and you'll get the order you want.

This looks almost like one of those cases where you have two bits of data (the alpha part and the numeric part) in the same field - are these whole product identification numbers or something?

 

[Jerome]

Ok this is what I've done so far. I've created two separate fields in the table. One for the Location (PLocation) of the part and one for the part number (pNumber).

I still use this code to pull and sort the data:

Public Sub GetData()

On Error Resume Next
Dim StrSql As String 'SQL statement for the record source

sText = Trim(Me.TextSearchString.Text)

StrSql = "SELECT * FROM TblParts "

'Is there any text to test?
    'If Not sText = "" And IsDelOrBack = False Then
                        
            StrSql = StrSql & "WHERE " & Me.ComboCategory.Column(1) & " Like '" & "*" & sText & "*" & "*' ORDER BY " & Me.ComboFilteredBy.Column(1) & ";"
                         
            'Refresh the rowsource with the new SQL
            Me.ListChemicals.RowSource = StrSql
                
    'End If
    'Requery the list box to show results
    Me.ListChemicals.Requery

End Sub

Now the data is sorted for only one column. Is it also possible to sort for a second column?

Thanks in advance.

 

[Galaxiom]

Now the data is sorted for only one column. Is it also possible to sort for a second column?

ORDER BY field1, field2, etc

 

[Jerome]

That's what I tried, but it doesn't work:

            StrSql = StrSql & "WHERE " & Me.ComboCategory.Column(1) & " Like '" & "*" & sText & "*" & "*' ORDER BY " & Me.ComboFilteredBy.Column(1) & ", " & Me.ComboFilteredBy1.Column(1) & ";"

 

[MStef]

Hello Jerome!
Look at "DemoMySortA2000.mdb" (attachment, zip).
Open form and try.
You don't need your VBA.

 

[Jerome]

Thanks for the demo. The code looks good! Never thought about theleft and mid function. The problem is I've already split it into two fields and can't change it easily.

The problem is that I let users decide which columns to sort using two combo boxes. Thats why I use VBA.

 

[Jerome]

Got it working. The problem was the table datatype for the numbers. It was set as text. I changed it to number and now it's working.