Converting a Number to Decimal

[lwilt]

I'm trying to convert a cost field from number to having a decimal place. So I want 1 to be 1.00 and 5.4 to be 5.40. If I go into the tables design view and change the field size to decimal and select how many decimal places it won't change anything the way I want. I tried using CDec function but it said invalid format.

Is there a function that will do this for me? thank you

[RuralGuy]

What is the current Field Type? Single, Double, Currency...

[lwilt]

It was double when I tried using the convert function CDec and then I changed the field type to decimal and selected 2 decimal places and 1 stayed at 1 instead of becoming 1.00. I also tried the function again at that point and it didn't work.

[RuralGuy]

As you know, trailing zeros in a numeric field are not significant. Displaying 2 decimals is simply a display option and can be set in the control that displays the value.

[ssanfu]

If you change the properties for the field in the TABLE from Double to Decimal, you also should change other properties:

FORMAT : Standard (<< how the data looks in the table)
SCALE : 2
DECIMALS : 2

Warning: When you change these, you could lose some data.

The format property (in the table) controls how the data looks when you view the table. Might look different in a control on a form/report.

[Dal Jeanis]

1) You're safer using Currency data type, if you care about accuracy and retaining exact numbers. Decimal doesn't guarantee results, accounting-wise.
2) If you're converting a field on your database, you should add the new field, monkey with it using update queries until you have the results you want, then delete the old field and rename the new one.

Do that in a junk copy of your database, and back up the prod one both before and after you port over the changed format.