Access 2000 Ascending Sort

[slifer666]

Hi,

I have a database of about 2300 entries and new ones are continually being added. However they are not always in numberical sequence, (eg. 604, 605, 606 etc.) sometimes they will be new revisions on an old number, (eg. 345R3, 604R1 etc)

I have noticed that in Access 2000, when you attempt to sort the numbers in to a numerical sequence, they dont always do it, they will go by the first number (eg. 1, 10, 100, 101 etc) How can i get it to go into a correct sequence (eg. 1, 1R1, 2, 3, 4 etc)??????

 

[KenHigg]

I'm guessing you'll need to parse out your number part to sort on...

Or the correct way (IMHO) is to change your way of thinking about the values. Don't think of them as numerical values, but as textual values. You can find the value you need if you understand how it is sorting...

 

[slifer666]

The Values are currently set at Text, if set as Number Access wont accept values such as 501R1

 

[KenHigg]

I understand that. Now start thinking of them as text instead of numbers.

 

[slifer666]

I understand that. Now start thinking of them as text instead of numbers.

That is Infinately Unhelpful!!!

 

[KenHigg]

Sorry. I didn't mean to be short....

As far as I know, there is no native functionality in Access to do what you need to do. So... To build a function to do this I would examine all of the combination of number and text characters you will encounter. Then you may be able to parse (split) out the leading numerical part of the data. Then you could sort on this value, etc,...

 

[slifer666]

Thanks for that, im just trying that now

Sorry for being short aswell, long day at workl!!!!

 

[slifer666]

Ok, so if i change it so that my database looks like this:

Field1 Field2
001
001 R1
120
010 R8
010 R2
098
098 R22
098 R11
006
006 R6

It should (after sorting) look like this:

Field1 Field2
001
001 R1
006
006 R6
010 R2
010 R8
098
098 R11
098 R22
120

 

[KenHigg]

I just built this in a db and did a simple text sort and it did what you say you need it to do..?

 

[slifer666]

Alright, cheers

Hopefully this will work properly now

Thanks Again

 

[Brianwarnock]

Hi mind if I butt in,
In your query enter 2 new fields to sort on. Lets call your original field ID2
then Expr1: Val(nz([id2],0)) and Expr2: Mid([id2],IIf(Val(nz([id2],0))=0,1,Len(Val([id2]))+1)) are the new fields . sort on these but you can of course click to not show.

Brian