Recordset order not woring with numbers

Posted on 2006-04-13
Last Modified: 2010-04-17
When I use order by on a acces text field that contains numbers the order does't appear to be working.
Is this because the text are numbers. Heres my code.
.RecordSource = "select * from [Family History] where  [Family #] ='" & FamilyNum & "' order by [Order Number] desc"
SHouldn't this arrange the records in descending order via the order number?
Question by:whiwex
    LVL 2

    Accepted Solution

    For numbers in a text field, you need to either left-fill the field with zeroes to get the order to sort correctly, as for example, 456 will sort after 1000, whereas 0456 will sort before 1000 (the comparison starts from the left-most character - ie "4" vs "1" in that example), or order by Val([Order Number]) instead.

    Using Val is not ideal, as it will have to perform that calculation on every row in the table.  It would be better to either left-fill the numbers, or just use a numeric field.
    LVL 44

    Expert Comment

    are you saying the the field named [Order Number] is defined as a TEXT field in the database, but that it actually contains DIGITS (notice I did not say 'numbers')

    the characters "1", "12", "123"  are quite different from the numbers 1,12,123

    for example the character sequence "123" will be sorted as LOWER that the character sequence "23"

    where , as NUMBERS, 23 is clearly LOWER that 123


    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    This is an explanation of a simple data model to help parse a JSON feed
    This is about my first experience with programming Arduino.
    Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    754 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

    18 Experts available now in Live!

    Get 1:1 Help Now