Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

Sort query in vb

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

Thanks
0
whiwex
Asked:
whiwex
5 Solutions
 
Éric MoreauSenior .Net ConsultantCommented:
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)

Cheers!
0
 
suprapto45Commented:
I will see what I can do for you :)

David
0
 
suprapto45Commented:
Hi,

order by [Certificate Number]"

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

David
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
dotmeatCommented:
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()
0
 
michael1174Commented:
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])
0
 
jacobhooverCommented:
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.
0
 
jacobhooverCommented:
Wish the author would of left some feedback for any of the suggestions...
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now