ordering items in an sql statement

[estellemorris]

Hi,

I am trying to order items, task 1, task 2, task 20, task 3 in my database using the order by function in my sql statement unfortunatly it orders it as task 1, task 2, task 20, task 3 not task 1, task 2, task 3, task 20 as I want it to. I believe that you cannot use the replace function within an sql statement unless you are using it directly within Access, any other idea?

 

[PHV]

Perhaps this ?
ORDER BY Val(Mid([field name],6))

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

 

[Golom]

If the field in question is always of the form

task xx​

with the numeric part starting in the sixth character position then

ORDER BY Val(Mid([TheField],6))

 

[estellemorris]

thought abut that or using right(thefield, 6) but unfortunatly it doesn't work as I am also using distinct in ym sql statement

 

[Golom]

Well

Right('Task 23',6) will produce ask 23

You don't want the rightmost 6 characters ... you want the characters starting with the sixth one to the end of the string. That's why PHV and I suggested the Mid function.