Solved

Sorting by number

Posted on 2015-02-01
8
93 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 (Access MVP)
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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 (Access MVP)
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 37

Accepted Solution

by:
PatHartman earned 500 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 50

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

724 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