I presently search a MSaccess dbase using SQL queries. I need to sort numerical data by ASC. How can I do this so that it searches

1,2,3,4,5,6,7,8,9,10,11,12,13 etc

rather than

1,10,11,12,2,3,4,5,6 etc



which is what happens at present ?

Store your numerical data in a numerical type (int, double, float, etc) and not a character type (char, varchar, text)?

Cheers,

Alex
--
Gossamer Threads Inc.

Forgive the ignorance, but are you referring to the method of storing the data in the dbase or in the query. ?

I forgot to mention but the numbers are actually of the format text+Number

ie VIC1,VIC2,VIC11 or SA1,SA2 etc



Thanks

DCP

He's refering to the column type. You need to set it to INT, SMALLINT or similar, and then it should order them in numeric order.

Cheers

Andy (mod)
andy@ultranerds.co.uk

---

Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!

If you are storing chars, then you need to 0 pad them as in VIC01, VIC02, VIC11, VIC12 (assuming it's a max of 99, otherwise you need two leading zeros). Otherwise split it up into two columns, one with vic and the other with 1, 2, etc.

Cheers,

Alex
--
Gossamer Threads Inc.

Would there be a need to create a new column to store VIC in every row?

Probably as it would be a mix of VIC and SA at a minimum (from the example he gave)..

Cheers,

Alex
--
Gossamer Threads Inc.

They will be stored together in the same column, and will be VIC, SA, QLD, NSW etc



The number component could reach as high as 100 000

You need to either:

1. Split the columns into a CHAR which contains the VIC, SA, QLD, etc and an INT which stores the 1,2,3, etc.

2. 0 pad the numbers so it stores things as VIC000001

3. See if you can write a custom sort function. I'm not sure how well access supports stored procedures, so I can't help you here. =)

Cheers,

Alex
--
Gossamer Threads Inc.

Thanks will give it a try and post the results.



Cheers

Hi Alex,

what if you're trying to store numbers with multiple decimals.
Like version numbers or codes as in:
1.1.1
1.1.9.1
1.1.10
1.2
1.10.1

I'm having a heck of time figuring out how to define the column type so that the above list is the result instead of
1.1.1
1.1.10
1.1.9.1
1.10.1
1.2


thanks in advance for your response.

peace.