Alter FIeld Size And Scale

fastcorvette · December 16th, 2003, 06:25 PM

How do I modify the following SQL statement to not only change the field type to number but to also change the field size to decimal and the scale to 2?

ALTER TABLE Table1 ALTER COLUMN Field1 Number

Steven · December 16th, 2003, 07:41 PM

What do you mean by "scale" - do you mean the number of decimal places? If so, then that's not really an SQL thing.

The statement you've got there turns the field into a Double - in other words, it can store up to 15 decimal places. The only other numeric fields that can handle decimal places are Single (7 decimal places) and Decimal (28 decimal places).
Regardless of how many decimal places show up, it will still store down to that precision.
So it's not actually a SQL thing, rather a VBA thing.
Before I go worrying about whether to write some code that will change it - is this what you're talking about - i.e. the number of decimal places displayed?
Steven

I am a loud man with a very large hat. This means I am in charge

fastcorvette · December 16th, 2003, 07:50 PM

Yes, I am talking about the number of decimal places. The SQL statement as it is sets the decimal places to zero. I have to go into the database in Access 2000 where the data type is correctly now "Number" but I have to change the field type to "Decimal" and the scale to "2" in order for the field to allow decimal places.

Steven · December 17th, 2003, 06:04 AM

I'm guessing some settings you have must be different to me, as I can't replicate the problem.
I created a table called Table1, with a field called Field1 (type=Text), and ran that statement, which turned the field into a double with Auto decimal places.
But regardless..
Try this:

Code:

Function thins()
Dim tdf As TableDef
Dim prp
Dim fld
With CurrentDb
For Each tdf In .TableDefs
If tdf.Name = "Table1" Then

For Each fld In tdf.Fields
    If fld.Name = "Field1" Then
       fld.Properties("DecimalPlaces") = 2
    End If
Next fld
End If
Next tdf

End With
End Function

Steven

I am a loud man with a very large hat. This means I am in charge