sorting within a field 1

[irethedo]

I have a table that has multiple items in a field delimited by a "-"
and I am looking for a way to sort through these.

For example here is what one field might look like:

- 007310 - ENCR - 015968 - 019838 - 10193 - REPORT - PERST

an I would like to put the items in this field (and for all other records)
in numerical order.

Is there an easy way to do this?

Thanks

 

[Andrzejek]

Why not just keep your data:

[pre]
PK SomeField
1 - 007310 - ENCR - 015968 - 019838 - 10193 - REPORT - PERST
2 ...
[/pre]
Like this - normalized way:

[pre]
FK SomeField
1 007310
1 015968
1 019838
1 10193
1 ENCR
1 PERST
1 REPORT
2 ...
[/pre]
Then you can sort it any way you want...

Would that work for you?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[irethedo]

Why not just keep your data:

PK SomeField 1 - 007310 - ENCR - 015968 - 019838 - 10193 - REPORT - PERST 2 ...

Like this - normalized way:
[pre]FK SomeField
1 007310
1 015968
1 019838
1 10193
1 ENCR
1 PERST
1 REPORT
2 ...[/pre]

Then you can sort it any way you want... Would that work for you?

Thanks Andy-

Because of the nature of how this data is currently stored,
it can vary from one record to the next and it would be easier to compare by
eyesight if these were organized in some sort of order

So instead of looking at these:
[pre]- 007310 - ENCR - 015968 - 019838 - 10193 - REPORT - PERST
- 015968 - 019838 - 10193 - 007310 - ENCR - REPORT
- 10193 - 015968 - 019838 - ENCR - PERST- REPORT[/pre]

It would be easier to view these like this:
[pre] - 007310 - 015968 - 10193 - 019838 - ENCR - PERST- REPORT
- 007310 - 015968 - 10193 - 019838 - ENCR - REPORT
- 015968 - 10193 - 019838 - ENCR - PERST- REPORT[/pre]

 

[MajP]

Andy's question is does the data have to be stored in a single string? I will assume it comes from some external source. If not then you should think about modifying the database and store as Andy suggests. I think I can write the code to do that. You would then run a function in query to get the sorted string. Then you would have to do an update query into a new field if you want it to persist. You may be able to just do the query and not persist the sort, but that could be really slow.

 

[MajP]

Public Function SortString(varString As Variant) As Variant
  Dim aValues() As String
  Dim i As Integer
  If Not IsNull(varString) Then
     aValues = Split(varString, "-")
     'PrintArray aValues
     BubbleSort aValues
     'PrintArray aValues
     For i = LBound(aValues) To UBound(aValues)
       If Not Trim(aValues(i)) = "" Then
         If SortString = "" Then
           SortString = "- " & Trim(aValues(i))
         Else
           SortString = SortString & " - " & Trim(aValues(i))
         End If
       End If
     Next i
  End If
End Function
Public Sub BubbleSort(ByRef aArray() As String)
  Dim strTemp As String
  Dim i As Long
  Dim j As Long
  Dim lngMin As Long
  Dim lngMax As Long
  lngMin = LBound(aArray)
  lngMax = UBound(aArray)
  For i = lngMin To lngMax - 1
    For j = i + 1 To lngMax
      If aArray(i) > aArray(j) Then
        strTemp = aArray(i)
        aArray(i) = aArray(j)
        aArray(j) = strTemp
      End If
    Next j
  Next i
End Sub

SELECT 
  tblStrings.UnsortedString, 
  sortString([UnsortedString]) AS sortedString
FROM 
  tblStrings
ORDER BY 
  sortString([UnsortedString]);

[tt]
UnsortedString	                                                sortedString
- 007310 - ENCR - 015968 - 019838 - 10193 - REPORT - PERST	- 007310 - 015968 - 019838 - 10193 - ENCR - PERST - REPORT
- 015968 - 019838 - 10193 - 007310 - ENCR - REPORT	        - 007310 - 015968 - 019838 - 10193 - ENCR - REPORT
- 10193  -   015968 - 019838 - ENCR - PERST- REPORT	        - 015968 - 019838 - 10193 - ENCR - PERST - REPORT[/tt]

 

[irethedo]

Thanks MajP

I have a couple of questions about this...

tblStrings = my table name
UnsortedString = the field in my table that I want to sort
sortedString = a new field in my table that will contain the sorted string

Question 1:
In your example, am I correct in assuming the following:

tblStrings = my table name
UnsortedString = the field in my table that I want to sort
sortedString = a new field in my table that will contain the sorted string

Question 2:
I have created the Public Function SortString and the Public Sub BubbleSort
but how do I execute the Select code?

So based upon my assumptions, my code looks like this:

'SELECT ord_tbl.SPNote, sortString(SPNote) AS sortedSPNote INTO sortedSPNote_tbl
'FROM ord_tbl
'ORDER BY sortString(SPNote)

If I try to do the following:

strQuery = "SortNote_qry"   
DoCmd.OpenQuery strQuery, acViewNormal, acReadOnly

I don't see any difference in the order

If I try this as a Query and run it in design mode, I get the message
"Undefined function 'sortString' in expression."

What am I doing wrong?

Thanks again

 

[MajP]

1) The functions need to go in a standard module, not a form's module. It will not be able to call a function in a form's module
2) Yes in my example the table name was "tblStrings" and the field with the string was "unsortedString"
3) when you use field names in a sql function you need brackets around the name so it knows
SELECT ord_tbl.SPNote, sortString([SPNote]) AS sortedSPNote INTO sortedSPNote_tbl
'FROM ord_tbl
'ORDER BY sortString([SPNote])

I ran the code from a sql query calling the function. You could write code to do the same. I used a select, but it works with an append as well.

 

[irethedo]

Thanks MajP

That works really nice.

Can this code be modified so that the new sorted field can completely replace the Ord_tbl.SPNote field?

 

[MajP]

Should be able to run an update query

Update ord_tbl
SET SPnote = sorstring([spnote])

Backup your data just in case, before running.

If possible, I would still look at normalizing your data as Andy suggested. This is a pretty big workaround to do a trivial sort. You can use the same code logic to create your normalized child table. As Andy showed, each item in the string is its own child record.