Sort query in vb

Posted on 2006-05-05
Last Modified: 2008-01-09
I have a query that I want to sort. The field That I want to sort is a Text field. I tried the following but neither sorts correctly.

.RecordSource = "select * from Inventory where  [Certificate Number] like '%" & CertificateNumAIF & "%' and [Vendor Name] ='" & VendorAIF & "' order by val([Certificate Number])"

.RecordSource = "select * from Inventory where  [Certificate Number] like '%" & CertificateNumAIF & "%' and [Vendor Name] ='" & VendorAIF & "' order by [Certificate Number]"

The certificate number contains the following values  None - 1  through None -15

Question by:whiwex
    LVL 69

    Accepted Solution

    Hi whiwex,

    And I suspect you receive values 1, 10, 11, 12, 13, 14, 15, 2, 3, ...

    There not much you can do but:
    -you can add another field for the sorting which will be fully numeric
    -you can add a 0 in front of the 1-9 numbers (None - 01, None - 02, ... None 10)

    LVL 16

    Expert Comment

    I will see what I can do for you :)

    LVL 16

    Assisted Solution


    order by [Certificate Number]"

    should work properly. Can you describe more about your problem?

    LVL 5

    Assisted Solution

    You can do like this

    .RecordSource = "select * from Inventory where  [Certificate Number] like '%" & CertificateNumAIF & "%' and [Vendor Name] ='" & VendorAIF & "' order by val(replace(replace([Certificate Number], 'None', ''), '-', ''))

    In this query, I replaced 'None' and '-' as a blank('') and convert the number by using val()
    LVL 2

    Assisted Solution

    Try using the IIF function to determine the length of Certificate Number.  If your highest number is in the hundreds, then you'll have to add two 0's to numbers 1-9 and one 0 to 10-99. Try Something like this:

    order by IIF(len([Certificate Number])=1,"00" & [Certificate Number], IIF(len([Certificate Number])=2, "0" & [Certificate Number],[Certificate Number])
    LVL 7

    Assisted Solution

    how about this idea:

    Depending on what backend database you are using create a "VIEW" that selects from the table but also creates a virtual column removing the "None -" portion and then converting the remaining value to a numeric value.

      This is similar to dotmeat's suggestion except you would be doing all the calculating on the server side instead of on the client.  This has several benefits over doing it on the client side, from being able to tweak the ordering without having to change the application to being able to upgrade the table to have a true numeric column in the future and removing the virtual column.

      Also, I would imagine it to be a vast performance boost to handle it all in the database on the server side (that's what a DB is designed for) verses retreiving all records to the client, then creating the virtual column on the client and then sorting on the client.  The performance boost will probably be mute if the table is only going to contain a few hundred records, but if it has the potential to grow in the future it will eventually get to a point where the client side method will be extremely slow.
    LVL 7

    Expert Comment

    Wish the author would of left some feedback for any of the suggestions...

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Article by: Nadia
    Suppose you use Uber application as a rider and you request a ride to go from one place to another. Your driver just arrived at the parking lot of your place. The only thing you know about the ride is the license plate number. How do you find your U…
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
    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 …

    734 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

    22 Experts available now in Live!

    Get 1:1 Help Now