split numbers & text

[Floodster]

I have a table with house numbers, now some of these are numbered 1A, 2B etc so my column has to be set as TEXT.
My problem is that when I query this & ORDER it on house number it goes 1, 10, 20 etc.
How can I split the numbers & text into 2 columns in a query or change it so it ORDERS properly.

cheers.

 

[DotNetGnat]

try...

ORDER BY Value(yourfield)

-DNG

 

[PHV]

ORDER BY Val([yourfield]), yourField

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Floodster]

tried that & got datatype mismatch in criteria expression

SELECT [Personal Visit].*, [Personal Visit].[House Number]
FROM [Personal Visit]
ORDER BY VAL([Personal Visit].[House Number]);
???

 

[PHV]

And this ?
ORDER BY Val(Nz([Personal Visit].[House Number]), "0")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886