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
Solved

Sorting by number

Posted on 2015-02-01
8
91 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)
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 10

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 47

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 35

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 49

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
database opened as read only 10 24
Access/Visual Basic Question 3 23
Get Next number from Stored Procedure 8 21
Excel VBA 30 30
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

766 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