SQL and sorting...

[noccy]

Hello!

I have a problem with sorting. I want to sort a recordset by part of the contents of a field.

The field has ARB and then a numeric value:
ARB1
ARB2
ARB3

etc

I want to order the records by the numeric value. I have tried something like:

"SELECT * FROM myTable ORDER BY MID(MyField,4,Len(MyField -3))", but without any luck....


Anyone know a good approach to this?

Thanks

noccy

 

[maxmangion]

are the letters in your field all ARB, because if this is the case, you can simply do:

Order By fieldname

 

[Mile-O]

are the letters in your field all ARB, because if this is the case, you can simply do:

Order By fieldname

Not if the ARB10, ARB11, ..., ARB20

as this would sort like this:

ARB1
ARB10
ARB11
ARB2
ARB20


Just take out that -3 from the SQL:

"SELECT * FROM myTable ORDER BY MID(MyField,4,Len(MyField))",

 

[maxmangion]

you're right SJ, i did not notice that his example was 1, 2, 3, 11, 12 etc ... i was refering if the data was 0001, 0002, 0003, etc.

 

[noccy]

Why take away -3?

noccy

 

[Mile-O]

Why take away -3?

MID(MyField,4,Len(MyField -3))",

For a start, you have it inside the Len function and not after it which is where I presume you really wanted it. If the field value is ARB1 then you are trying to do a mathematical formula on it -> ARB1 - 3 which can't be done.

Now, the Mid function. You are getting the number from your field by detailing the field, the starting point (4th character). You are using the Len function to determine the length of the field but you don't actually need the arguement - if you don't use it then to the end of the field is assumed.

With that, I left the Len(MyField) bit in your line above as it will do the same thing. In fact, all three of these will work:

• MID$(MyField,4)
• MID$(MyField,4,Len(MyField))
• MID$(MyField,4,Len(MyField)-3)

Use the first one. And not that I changed the function from MID to MID$ - this is function specific to text (which helps it run faster, although not noticably to you) as the MID function has to convert any other possible data type to a String.

 

[noccy]

That works, but I still get ARB1, ARB10, ARB11......ARB2, ARB20....


noccy

 

[maxmangion]

your field is still being used as text that's way. Try using the Val() Function ... Val(MID(MyField,4))

 

[noccy]

I have tried val(), but I use asp.net, and I get an error saying the val function does not exist.....


noccy

 

[maxmangion]

well i do not know it's equivalent in asp, but it works fine with access because i've tried it out. try searching for val() equivalence in ASP.

 

[Mile-O]

That works, but I still get ARB1, ARB10, ARB11......ARB2, ARB20....

Of course you are. That's because you are only ordering by the numbers. You have not made a calculate field but you are, instead, selecting all your fields.

ie. SELECT * FROM myTable

You obviously want:

SELECT *, MID$(MyField,4) As NewField
FROM myTable
ORDER BY MID$(MyField,4);

 

[noccy]

Ok, Thanks. I will try that approach =)