Solved

sorting records in access 2000 query within vb6.0

Posted on 2008-11-01
7
453 Views
Last Modified: 2013-12-25
I developed a query in access2000 using an existing table, displayed the query using sql view, then copied the query into a vb6.0 program.  The query sorts a column in ascending order.  The column is a text field. The information in the column of the records varies in length from 1 to 2 positions.

For example, the data in the column of some of the records  is 1.  In other records the data might be 11, or 12 or 2 or 3, etc..  

I would like the the sort to arrange the records with the 2 ahead of the records with 11, however, the query sorts as follows:

1
1
11
12
2
3
etc.

I would like for it to be
1
2
3
11
12
etc.

How can I accomplish this?

 
0
Comment
Question by:morrisbo
[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
7 Comments
 
LVL 18

Expert Comment

by:jmoss111
ID: 22860345
Hello morrisbo,

You could convert the column using CLng([MyColumnName]) then it would sort correctly

Regards,

Jim
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22860453
I agree with Jim.  This is how I would do this as well.  It works in most SQL environments to just convert in order by statement.

SELECT Col1, Col2, Col3
FROM TableName
ORDER BY CLng(Col1);

OR Val(Col1)

Again, this is just an extension of what Jim already told you -- his answer is correct.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 22861267
If the column you are sorting on only ever contains numbers, why is the column type declared as Text in the database.  If it were declared as a number type, then the sort would work properly, without the need to convert the column data in the SQL.

AW
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:morrisbo
ID: 22862651
Thanks for the input.

I mispoke about the data.  It was declared as text as some of the records contain values other than numbers.

It actually contains Insurance Territories in Texas

example:

1
2
3
4
5
6
11
15
15C
etc.

I think I can work with this though.  Just need to convert the values with Alpha characters to some unizue number that will cause it to sort correctly, then convert it back when printing the results.

Thanks,

I will give it a try.

morrisbo
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 22862681
The VAL function will return the numbers portion of a column, so if the value is always numbers followed by a letter you can do this in the order by.
+Orders by number portion as a number
+Orders by field as text within each number
SELECT Col1, Col2, Col3
FROM TableName
ORDER BY VAL(Col1), Col1;
 
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22862707
If some of the values are numbers and the others that are text can be any pattern, then do this to sort numbers and text independently:
SELECT Col1, Col2, Col3
FROM TableName
ORDER BY IIF(IsNumeric (Col1), VAL(Col1), 0), Col1;
If you want text to appear after numbers, then you substitute the 0 for big number (i.e. bigger than max value of number based codes, so if most 2 digits -- 999 would be big in comparison).  Again, add second sort on the column itself to handle alpha sort.
0
 

Author Comment

by:morrisbo
ID: 22871173
I created a column expression val([Territory}) and made this the major sort ascending, then sorted on the existing Territory next.  This gave the result I needed.

Thanks for all the help.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

730 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