Solved

Sorting by number

Posted on 2015-02-01
8
87 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
8 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 10

Expert Comment

by:HuaMinChen
Comment Utility
Use

...
order by val(column1)

Open in new window


or

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

Open in new window

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
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
Comment Utility
Thank you!!
Derek
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now