Can't properly sort Decimal datatype in a Query.

[jackal63]

I've got a database that pulls tables from FoxPro over ODBC. Apparently FoxPro uses decimal data types for the numbers, or that could just have been the programmers preferance. I've got query that forms some associations between these tables and has a calculated field called "scrap" which seems to have inherited the Decimal data type from the fields it was calculated from. Anyways, when I try to sort on this field I get really silly results like; 9.6%, 95.1%, 8.2%, 75.4%. Obviously it is sorting the numbers irregardless of where the decimal place is in the number. I tried using another calculated field which was just Cdbl([scrap]) but any conversion function besides Cstr just generates errors when the Query is run. And the resultant Strings sort in the same, wrong way.

Any helpful suggestions on this would be extremely appreciated. I double checked the ODBC drivers I was using for FoxPro and there are no other ones that Windows will install for it.

 

[jhall156]

The column is either a string or a variant string coming out of ODBC and so is sorting as such. I suspect that wrapping a VAL() around the column might help, in any case can you provide the SQL? JHall