|
Subject:
|
Alter FIeld Size And Scale
|
|
Posted By:
|
fastcorvette
|
Post Date:
|
12/16/2003 5:25:20 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
|
|
Reply By:
|
Steven
|
Reply Date:
|
12/16/2003 6:41:26 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
|
|
Reply By:
|
fastcorvette
|
Reply Date:
|
12/16/2003 6:50:05 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.
|
|
Reply By:
|
Steven
|
Reply Date:
|
12/17/2003 5:04:37 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:
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
|
