sorting problem

[Mr. T.]

Hi,

i have a table with adresses in it. The house number is a text field. The
reasen for that is that house numbers can have a letter attached to it. For
example, you can have a house number 50 a number 50a and 50b.

Now when i do my query and want the numbers ordered, because of the
housenumberfield being a text, it doesn't order correctly. If f.e. i have
the following house numbers in my table : 1, 2, 5, 10, 20, 40
it will return the list as follows: 1, 10, 2, 20, 40, 5
when what i really want is 1, 2, 5, 10, 20, 40

anyone has a simple solution to that problem? This is the sql i'm using:

sql = "SELECT [Nr], [BVNr] FROM [adressen] WHERE [Street] = " & """" &
Street & """"

Thx in advance & kind regards!

Thomas L.

 

[RobFMS]

You will need to use the VAL() function. This will read all numbers and stop
at the first alpha character.
You will need to do a little manipulating of the query in order to sort the
records.

In the case of my sample, this is what my data looked like:
1
2
3
4
4a
4b
1a
2b
3c

When I wrote the query, I created two columns:

Column 1:
This is where I execute the VAL() function. This will display only the
numbers.
In the ORDER BY, I list VAL(Table2.Nums) so that it orders the numbers
sequentially.

Column 2:
This is where I display the data. I also include this in the ORDER BY.

SELECT Val(Table2.Nums) AS TestSort, Table2.Nums
FROM Table2
ORDER BY Val(Table2.Nums), Table2.Nums;

The end result is this:

TestSort Nums
1 1
1 1a
2 2
2 2b
3 3
3 3c
4 4
4 4a
4 4b


If you notice, the column "Nums" puts the ordering in the proper manner.

HTH


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Mr. T.]

Hi,

this was what i needed!

THx very much!!

Thomas

You will need to use the VAL() function. This will read all numbers and stop
at the first alpha character.
You will need to do a little manipulating of the query in order to sort the
records.

In the case of my sample, this is what my data looked like:
1
2
3
4
4a
4b
1a
2b
3c

When I wrote the query, I created two columns:

Column 1:
This is where I execute the VAL() function. This will display only the
numbers.
In the ORDER BY, I list VAL(Table2.Nums) so that it orders the numbers
sequentially.

Column 2:
This is where I display the data. I also include this in the ORDER BY.

SELECT Val(Table2.Nums) AS TestSort, Table2.Nums
FROM Table2
ORDER BY Val(Table2.Nums), Table2.Nums;

The end result is this:

TestSort Nums
1 1
1 1a
2 2
2 2b
3 3
3 3c
4 4
4 4a
4 4b


If you notice, the column "Nums" puts the ordering in the proper manner.

HTH


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Hi,

i have a table with adresses in it. The house number is a text field. The
reasen for that is that house numbers can have a letter attached to it. For
example, you can have a house number 50 a number 50a and 50b.

Now when i do my query and want the numbers ordered, because of the
housenumberfield being a text, it doesn't order correctly. If f.e. i have
the following house numbers in my table : 1, 2, 5, 10, 20, 40
it will return the list as follows: 1, 10, 2, 20, 40, 5
when what i really want is 1, 2, 5, 10, 20, 40

anyone has a simple solution to that problem? This is the sql i'm using:

sql = "SELECT [Nr], [BVNr] FROM [adressen] WHERE [Street] = " & """" &
Street & """"

Thx in advance & kind regards!

Thomas L.