morrisbo
asked on
sorting records in access 2000 query within vb6.0
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?
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?
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.
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.
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
AW
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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.
Thanks for all the help.
You could convert the column using CLng([MyColumnName]) then it would sort correctly
Regards,
Jim