?
Solved

Sorting by number

Posted on 2015-02-01
8
Medium Priority
?
94 Views
Last Modified: 2016-02-11
Whenever I print a report that that I want listed in numerical order, Access insists on listing 10 before 2 i.e.

1
10
2
3
4

I have to use a text box as Text because sometimes the list is:
D1
D10
D2
D3
D4
Can this be changed
0
Comment
Question by:DatabaseDek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40582295
in the first instance, where you simply have a number saved in a text datatype, you can use an expression as one of the sort by criteria, and then use the clng([fieldname]) function as the expression to change that value to a number.

When you have a leading alpha character, you can combine clng() with mid(), something like:

clng(mid([FieldName], 2))

Keep in mind, that whenever you use the NZ( ) function in a query, it will return a string value rather than a number, so you may need to explicitly type those fields using one of the conversion functions.
0
 

Author Comment

by:DatabaseDek
ID: 40582302
That works great where there is a number but when text only I show errors Also if I have A: CLng(Mid([DNumber],2)) curiously that works with A1 and A10 but if I use A: CLng(Mid([DNumber],3)) the same values come up "Error"
0
 

Author Comment

by:DatabaseDek
ID: 40582310
I see that the start point of the Mid is dictated by the ,2 so where customers can call a component any combination of text and numbers it is not going to be possible to do this I guess. Although the numbers will always be together we would need a way of isolating the numbers from the text and then sorting. That's not going to be easy. I have also found that using the QBE grid that the new field cannot be sorted anyway.

Tough one this!
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 11

Expert Comment

by:HuaMinChen
ID: 40582334
Use

...
order by val(column1)

Open in new window


or

...
order by val(mid(column1,2,4))

Open in new window

0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40582445
sure you can, but doing it in the query won't help, you must set the sort order in the report itself.  The reports sort order overrides query sort order.
0
 
LVL 38

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 40582594
I had to solve this problem for a Drawing Log application.  The company used a variety of naming formats depending on the type of drawing but they always wanted the "number" part to sort as a number rather than as a text string.  I've included the procedure plus three functions I had to create to replace the standard VBA functions which don't work the way I need them to for this task.  I made a decision that only Drawing Numbers of 20 characters or less would be reformatted.  Longer numbers were untouched.
So, in the queries/reports/forms, sort on funcRJust() rather than the field you are displaying.
Also, since the reformatted value wasn't displayed on the subform, I had to add some sorting features since the user couldn't use the built in stuff to sort by something he couldn't see.
Public Function funcRJust(strvalue As Variant) As Variant
Dim strBuild As String
Dim iLength As Integer
Dim i As Integer
Dim Filler As String
Dim Filler2 As String
Dim strNumbers As String
Dim strLetters As Variant
Dim strPrefix As String
Dim strRest As Variant

'Right justifying numeric portions of drawing numbers required the creation of three functions to replicate
'VBA functions that didn't work correctly for this purpose.

'IsAlphabetic() - needed to extract ONLY letters.  VBA doesn't have a function that does this.
'PatsFindFirstNumber - finds the first numeric character in a string
'PatsVal - replacement for the Val function which produces strange results when presented with strings like (Val(3e1.4) returns 30)
'IsNumeric - this VBA function cannot be used for our purpose except for strings of 1 char in length since it thinks 2D1 is numeric.
'The letters d and e followed by a number cause strange results as to a decimal point or dash.


'right justify drawing numbers of 20 characters or less
    funcRJust = strvalue
    If funcRJust & "" = "" Or Len(strvalue) > 20 Then   'ignore strings > 20 char
        Exit Function
    End If
    If IsAlphabetic(Left(strvalue, 1), False) = True Then   'handle "E" numbers separately
        If IsNumeric(Mid(strvalue, 2, 1)) = True Then
            GoSub FormatENumbers
        End If
        Exit Function
    End If
''    If IsNumeric(strvalue) Then   'isnumeric thinks 2D1 is numeric???
''        strNumbers = strvalue
''    Else
        strNumbers = PatsVal(strvalue)
''    End If
''''    strNumbers = Trim(strNumbers)
    If strNumbers & "" <> strvalue & "" Then
''''        If strNumbers < 0 Then  'bug in Val() returns .3 for 3D-1
''''            strNumbers = strNumbers * 10
''''        End If
        strLetters = Mid(strvalue, Len(strNumbers) + 1)
    Else
        strLetters = ""
    End If
''''    strNumbers = LTrim(strNumbers)
    iLength = Len(strNumbers)

    strBuild = ""

    For i = iLength To 1 Step -1
        strBuild = Mid(strNumbers, i, 1) & strBuild
''''        Debug.Print IIf(Mid(strNumbers, i, 1) > 3000, True, False)
    Next i

    Filler = Space(20 - Len(strNumbers))
    Filler2 = Space(20 - Len(strLetters))
    funcRJust = Filler & strBuild & Filler2 & strLetters
ExitProc:
    Exit Function
FormatENumbers:
    strPrefix = Left(strvalue, 1)
    strRest = Mid(strvalue, 2)
    strNumbers = PatsVal(strRest)
    If strNumbers & "" <> strRest & "" Then
        strLetters = Mid(strRest, Len(strNumbers) + 1)
    Else
        strLetters = ""
    End If
    iLength = Len(strNumbers)
    strBuild = ""
    For i = iLength To 1 Step -1
        strBuild = Mid(strRest, i, 1) & strBuild
    Next i
    Filler = Space(10 - Len(strNumbers))
    funcRJust = strPrefix & Filler & strBuild & strLetters
    Return
End Function

Public Function PatsVal(str As Variant) As String
'''''''''''Written to replace the VB Val() function which returns undesirable results (for our purposes) when the string includes
'''''''''''a d or e followed by another number - 3d4 or 1e3.2 for example.  VBA interprets the e and d as scientific notation.
'''''''''''IsNumeric() has a similar problem but we are using it in this function to analyze only a single character
'''''''''''at a time and that works fine.

    Dim strNumbers As String
    Dim i As Integer
    Dim ValLen As Integer
    Dim EndLoop As Boolean
    
    ValLen = Len(str)
    If ValLen = 0 Then
        PatsVal = ""
        Exit Function
    End If
    
    EndLoop = False
    i = 1
    
    Do Until EndLoop = True
        If i > ValLen Then
            EndLoop = True
        Else
            If IsNumeric(Mid(str, i, 1)) Then
                strNumbers = strNumbers & Mid(str, i, 1)
                i = i + 1
            Else
                EndLoop = True
            End If
        End If
    Loop
    PatsVal = strNumbers & ""
    
End Function
Public Function PatsFindFirstNumber(str As Variant) As Integer

    Dim strNumbers As String
    Dim i As Integer
    Dim ValLen As Integer
    Dim EndLoop As Boolean
    
    ValLen = Len(str)
    If ValLen = 0 Then
        PatsFindFirstNumber = 0
        Exit Function
    End If
    
    EndLoop = False
    i = 1
    
    Do Until EndLoop = True
        If i > ValLen Then
            PatsFindFirstNumber = 0         'none found
            EndLoop = True
        Else
            If IsNumeric(Mid(str, i, 1)) Then
                PatsFindFirstNumber = i     'number found at i
                EndLoop = True
            Else
                i = i + 1
            End If
        End If
    Loop
    
End Function
Function IsAlphabetic(char As String, Optional IncludeNumbers As Boolean) As Boolean
    If Len(char) <> 1 Then      'check length
        IsAlphabetic = False
        Exit Function
    End If
    If (Asc(char) >= 65 And Asc(char) <= 90) Or (Asc(char) >= 97 And Asc(char) <= 122) Then
        IsAlphabetic = True
    Else
        If IncludeNumbers = True Then
            If Asc(char) >= 48 And Asc(char) <= 57 Then  '0-9
                IsAlphabetic = True
            Else
                IsAlphabetic = False
            End If
        Else
            IsAlphabetic = False
        End If
    End If
End Function

Open in new window

0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40582674
In your report, you can sort on this expression which skips the leading non-numeric characters:

    StrReverse(Val("9" & StrReverse([DNumber])))

/gustav
0
 

Author Closing Comment

by:DatabaseDek
ID: 40591916
Thank you!!
Derek
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question