Sort in query

[Parkroyal]

Ok heres a good one. I'ts probably just me being blond I'm sur there is a simple answer.

I have a table with an alphanumeric field that I need to sort.The field is formated as text.

There can be between one and six numbers leading the data.
There can also be zero to three letters trailing the data.
i.e. 1039; 237644; 7PV; 16; 641PV; 982A; 1039PA; Etc, etc.

I need to run a query based on this field.
When the query is run it needs to display the data Numeric first; folowed by alphanumeric.

All need to be in descending order and alphanumeric needs to be grouped alphabetically as well as numericaly desend
I.e the above examples should be listed:

16
1039
237644
982A
1039PA
7PV
641PV

I've tried doing this in a query but can't get my head around the sort criteria to get it to display in the right order.

I cannot alter the format of these alphanumeric identifiers because it is historic information and needs to remain as is.

There are approx 18000 records to sort.

I've looked at spliting the field and then re concatanating after a sort but I can't get that to work either.

Any ideas?

Would it be better to do this in code rather than an Access query? and if so, How?


Cheers

 

[MichaelRed]

with 18K records there are probably more variations in the scheme than shown in your sample so this is probably a 'first shot'. 1 to 6 numerals plus 0 to three 'letters' is a total of 9 possible characters. Since it is an already mixed (alpha-numerics) field, you can be reasonably sure there are some which do not follow the pattern, so an initial prospect would be to just (right) pad the string with sufficient characters to get the uniform length. Since you expect the nemerics to be first, use the zero character as the pad. While there are some more robust pad routines on this site (search for "basPad") a simple routine will work for a one time use:

Public Function basPad(strIn As String) As String

        basPad = Right(String(9, "0") & strIn, 9)

End Function

Of course, as 'illustrated' this doesn't need to be a UDF at all, but could be directly placed in a query as an IIF. On the other hand the generic technique is often useful and may easily be expanded in a function to include various options (which end of the string to pad, what length to pad it to, what character to pad with, etc.)




MichaelRed
mlred@verizon.net

 

[dhookom]

I'm not sure I understand the basic logic of your order.
It looks like the alpha characters are the primary sort with the numerics being second. You xould create a user defined function as follows:

Function CreateSort(pstrText As String) As String
    Dim strTextPart As String
    Dim strNumPart As String
    Dim intChar As Integer
    Dim strChar As String
    For intChar = 1 To Len(pstrText)
        strChar = Mid(pstrText, intChar, 1)
        If IsNumeric(strChar) Then
            strNumPart = strNumPart & strChar
         Else
            strTextPart = strTextPart & strChar
        End If
    Next
    strNumPart = Right("00000000" & strNumPart, 8)
    strTextPart = Right("      " & strTextPart, 6)
    CreateSort = strTextPart & strNumPart
End Function

Your query would then sort on the expression:
SortOn: CreateSort([TextField])
You could also just do this in your report sorting and grouping
=CreateSort([TextField])

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[scriverb]

I don't understand your order necessity but I believe, although not very elegeant, that these functions and query SQL will give you the sort of this field that you need.

Public Function basPad(strIn As String) As String

        basPad = Right(String(9, " ") & strIn, 9)

End Function

Public Function FindNonAlpha(strIn As String) As Integer
Dim i As Integer
For i = Len(strIn) To 1 Step -1
    If IsNumeric(Mid$(strIn, i, 1)) Then
        FindNonAlpha = i
        Exit Function
    End If
Next i
End Function

SQL to sort field properly:

SELECT Table1.String_Value, IsNumeric([Table1]![String_Value]) AS Sort1, IIf(IsNumeric([Table1]![String_Value]),basPad([Table1]![String_Value]),0) AS Sort2, IIf(Not IsNumeric([Table1]![String_Value]),Mid$([Table1]![String_Value],FindNonAlpha([Table1]![String_Value])+1),0) AS Sort3, IIf(Not IsNumeric([Table1]![String_Value]),basPad(Mid$([Table1]![String_Value],1,FindNonAlpha([Table1]![String_Value]))),0) AS Sort4
FROM Table1
ORDER BY IsNumeric([Table1]![String_Value]), IIf(IsNumeric([Table1]![String_Value]),basPad([Table1]![String_Value]),0), IIf(Not IsNumeric([Table1]![String_Value]),Mid$([Table1]![String_Value],FindNonAlpha([Table1]![String_Value])+1),0), IIf(Not IsNumeric([Table1]![String_Value]),basPad(Mid$([Table1]![String_Value],1,FindNonAlpha([Table1]![String_Value]))),0) DESC;

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[scriverb]

dhookum: sorry about that, we were working and posting at the same time.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[Parkroyal]

Thanks for the responses guy's but I'm still in trouble. I've been working on this for days now and it's realy beging to bug me

Can't get any of these to work. prob just me being thick.

Bob Scriver I would like to go with your idea but the SQL doesn't go. The bit I'm confused on is

SELECT Table1.String_Value, IsNumeric([Table1]![String_Value]) AS Sort1,

I don't see how or where Table1 is converted to a string! THe code prompts for the string value but even when I manually insert that then the SQL still won't run properly. I just get a table full o '0's.

You say you don't understand the sort---It's easy just hard to explain: Numbers with no letters atached need to appear in the list first. Folowed by alphanumerics that ar grouped alpabeticaly with the number part sorted in order i.e. 700CA would come before 7CB because even although the number is smaller the alpha dictates the first sort order.

Logic would say, therefore, that if there is no alpha charaters then that groupe would come top of the list.

Cheers

 

[scriverb]

My reference to [Table1].[StringValue] is only an example and should be changed to yourtablename and the field name of the field that you want properly sorted. We never really established those particulars here.

As for my not understanding, I understand what you want and have provided a way to get it but I don't understand the why. But, it is your database and obviously you need it so here it is. Not putting it down or anything but sometimes we on this end can provide something for you without knowing the why. I was just making a rhetorical statement.

Good luck. Just make the adjustments to the code and run as direct. It works here with a table called Table1 and a field called StringValue. Just change it to your stuff and run it.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[dhookom]

ParkRoyal,
No offense but the function that I wrote and tested worked perfectly on the sample records that you provided. Did you try my solution? Did it work? Do you have question regarding the solution?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Parkroyal]

OK thanks guy's it finally clicked.
I'm a dork.

Bob

I changed the table name but never changed the field name. Doh. Trouble is I was trying to understand what was actually happening.-----No wonder I couldn’t work it out. Now I understand and have learnt a heck of a lot about SQL in trying to work it out.

Thanks it not only works like a charm but now I understand how and why it works.

Which leads me to:

Duane

Yes your solution does work. Bob provided me with the SQL and when I looked at it I realised that I hadn't got a clue what was actually going on. Now I know and have just written a query to go with your function. And yours also works like a charm.

As for the reasons why? Well just please take it as read that it is necessary. I wouldn't waste time on it if it wasn't.

Once again, thanks guys. You’ve helped me out and taught me a couple of valuable lessons along the way

Cheers

Pete