Prob using decimals and performing OrderBy

[gonecat]

I have numbers in my Access 2k db that may be either a single digit (4) or a decimal (4.5).

Since i am trying to do an ORDER BY in coldfusion, i guess i have to change this Datatype from Memo to Number, but i am not sure what the FieldSize, Format or Decimal Settings should be.

I have played around with them, but it just seems to 'get rid' of my decimal, making a 6.8 into just 6


Any help is much appreciated.

 

[jebry]

Hi!

I'm not very familiar with Cold Fusion but couldn't you do the order by on the field times 10 or even 10000?

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[rjoubert]

Field Size - Double
Format - Fixed
Decimal Places - set to a high enough number so you don't get your values truncated

 

[gonecat]

thanks for the replies.

Jebry: I wouldn't know how to do that sort of thing with the Order By.

RJoubert: I did that, but my prob is this now: If the number is 5, it is showing it as 5.0. I only wanted to show the decimal if there is infact a decimal number.

any suggestions on that?

 

[rjoubert]

You could use General Number instead of Fixed for the Format, but that would ignore whatever number you put in for Decimal Places.

 

[rjoubert]

Or you could use Decimal for the Field Size, General Number for the Format, then set the Scale and Decimal Places appropriately. Scale is the max number of digits that will be stored after the decimal point, and Decimal Places is the max number of digits that will be displayed after the decimal point. I just tested this, and any whole number entered into the field does not display the ".0" after it.

Good Luck!

 

[PHV]

Unless I completly misunderstood the issue:
ORDER BY Val([your field name])

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

 

[gonecat]

thanks a lot for everyone's help. i couldn't get anything to work for me so i did it at the coldfusion level, which works great:

SELECT *, Format(isize,'General Number') As FormattedPrice FROM tbl_options
and then display the number as #FormattedPrice#

Thanks again to everyone who helped