WRONG RESULT WHEN ORDERING NUMBERS IE 1>10>11>2>21 ETC

[si013]

I am currently running a passthrough query against an oracle database, doing a calculation then returning the results. With an order by clause on a specified field.

If the user then sorts a different field then trys to sort the original field the order follows the following order

1>1>11>18>2>21>26 etc

Obviously using a passthrough query i can not use the Clng() function which i have used in a previous database to fix this same problem.

I put a to_number() (oracle function) in the passthrough query so its not that.

I have put a =clng([fieldname]) on the subform still no success.

at a loss now??

Any help would be well appreciated!!!!!!!!!! Thanx in advance if you do.

 

[RoyVidar]

I don't know, by what you say, it seems the initial sort is based on the Oracle pass-through, but later sorts is based on Access. Could it be an option to requery the recordset in stead of sorting when one wishes the original sort order?

Could it be possible to base an Access query on the returned pass-through, where you use one of the number converting functions (val, int, clng...)?

Roy-Vidar

 

[HarleyQuinn]

How about adding a leading zero to the number if it is only one digit in your original query? Not sure if that would cause you problems or not but it might work for you?

select decode(length(your_field),1,'0' ||''|| your_field,your_field)
from your_table

I've never used to_number() but having a brief look at it I seem to notice that it strips of any leading zero's (similar to the way Excel can), this could account for your subsequent ordering problems.

Just an idea, hope it helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[si013]

i have fixed this by creating another query which queries the original query. This means that i can then use the access funtions ie clng() which will convert the number correctly.

Thank you for you pointers they both helped.

regards
me