INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

sorting within a field

sorting within a field

(OP)
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

RE: sorting within a field

Why not just keep your data:

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

FK SomeField
1  007310
1  015968
1  019838
1  10193
1  ENCR
1  PERST
1  REPORT
2  ...
 
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.

RE: sorting within a field

(OP)

Quote:


Why not just keep your data:

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

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


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:
- 007310 - ENCR - 015968 - 019838 - 10193 - REPORT - PERST
 - 015968 - 019838 - 10193 - 007310 - ENCR - REPORT 
 - 10193  -   015968 - 019838 - ENCR - PERST- REPORT 


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

RE: sorting within a field

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.

RE: sorting within a field

CODE -->

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 

CODE -->

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

CODE -->


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 

RE: sorting within a field

(OP)
Thanks MajP

I have a couple of questions about this...

Quote:


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:

CODE -->

'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:

CODE -->

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

RE: sorting within a field

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.

RE: sorting within a field

(OP)
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?

RE: sorting within a field

Should be able to run an update query

CODE -->

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close