How to Append Numeric Column with Decimal Places? 
Author Message
 How to Append Numeric Column with Decimal Places?

Hi.

I'm trying to create a new numeric field in an existing
Access table.  I cannot seem to figure out how to set the
Decimal Places property (NumericScale).  

When I try this:

    Set colColumn = New ADOX.Column

    colColumn.Name = "New Col"
    colColumn.Type = adSingle
    colColumn.NumericScale = 2
    colColumn.Properties("Nullable") = True

    ' append the column to the table
    Call tblTable.Columns.Append(colColumn)

The column is added without any errors but the
NumericScale is still 0 and when I look in Access, it
says "Auto".

If I go directly into Access and create a table with a
field of Single with 2 Decimal Places and then do this:

    Dim tblOD As ADOX.Table
    Dim colLoop As ADOX.Column
    Set tblOD = catCatalog.Tables("TestTable")

    For Each colLoop In tblOD.Columns
            MsgBox "Column: " & colLoop.Name & vbCr & _
                "Numeric scale: " & _
                    colLoop.NumericScale
    Next colLoop

    Set tblOD = Nothing

I always get the NumericScale = 0.

Thanks!



Sun, 27 Nov 2005 02:33:11 GMT  
 How to Append Numeric Column with Decimal Places?
Hi Mike,

In case of adSingle or adDouble datatype you cannot specify number of
decimal places. You could do that do adNumeric or adDecimal datatypes

colColumn.Type = adDecimal

--
Val Mazur
Microsoft MVP


Quote:
> Hi.

> I'm trying to create a new numeric field in an existing
> Access table.  I cannot seem to figure out how to set the
> Decimal Places property (NumericScale).

> When I try this:

>     Set colColumn = New ADOX.Column

>     colColumn.Name = "New Col"
>     colColumn.Type = adSingle
>     colColumn.NumericScale = 2
>     colColumn.Properties("Nullable") = True

>     ' append the column to the table
>     Call tblTable.Columns.Append(colColumn)

> The column is added without any errors but the
> NumericScale is still 0 and when I look in Access, it
> says "Auto".

> If I go directly into Access and create a table with a
> field of Single with 2 Decimal Places and then do this:

>     Dim tblOD As ADOX.Table
>     Dim colLoop As ADOX.Column
>     Set tblOD = catCatalog.Tables("TestTable")

>     For Each colLoop In tblOD.Columns
>             MsgBox "Column: " & colLoop.Name & vbCr & _
>                 "Numeric scale: " & _
>                     colLoop.NumericScale
>     Next colLoop

>     Set tblOD = Nothing

> I always get the NumericScale = 0.

> Thanks!



Sun, 27 Nov 2005 20:08:04 GMT  
 How to Append Numeric Column with Decimal Places?

Val,

Thanks for the response but no luck...

Here's what I tried:

    colColumn.Type = iType
    colColumn.NumericScale = iDecimalPlaces
    colColumn.Precision = iPrecision
    Call tblTable.Columns.Append(colColumn)

I've tried every combination of:
iType = adDecimal and adNumeric
iPrecision = 0, 5, 7, 10, 15
iDecimalPlaces = 0, 1, 2, 3

When I try adDecimal or adNumeric I get a run-time error
with an Invalid Type.  If I don't set the Precision
property, I get Invalid Precision and then Invalid Type.

The only way I can add the column is if I specify a "real"
Type (adDouble, adSingle), but it won't allow me to set
the Decimal Places property.  And, the Decimal Places
property is not one I can get to through .Properties()
method.

Any other ideas?  I'm using vb6 with Access 97.

Quote:
>-----Original Message-----
>Hi Mike,

>In case of adSingle or adDouble datatype you cannot
specify number of
>decimal places. You could do that do adNumeric or
adDecimal datatypes

>colColumn.Type = adDecimal

>--
>Val Mazur
>Microsoft MVP



>> Hi.

>> I'm trying to create a new numeric field in an existing
>> Access table.  I cannot seem to figure out how to set
the
>> Decimal Places property (NumericScale).

>> When I try this:

>>     Set colColumn = New ADOX.Column

>>     colColumn.Name = "New Col"
>>     colColumn.Type = adSingle
>>     colColumn.NumericScale = 2
>>     colColumn.Properties("Nullable") = True

>>     ' append the column to the table
>>     Call tblTable.Columns.Append(colColumn)

>> The column is added without any errors but the
>> NumericScale is still 0 and when I look in Access, it
>> says "Auto".

>> If I go directly into Access and create a table with a
>> field of Single with 2 Decimal Places and then do this:

>>     Dim tblOD As ADOX.Table
>>     Dim colLoop As ADOX.Column
>>     Set tblOD = catCatalog.Tables("TestTable")

>>     For Each colLoop In tblOD.Columns
>>             MsgBox "Column: " & colLoop.Name & vbCr & _
>>                 "Numeric scale: " & _
>>                     colLoop.NumericScale
>>     Next colLoop

>>     Set tblOD = Nothing

>> I always get the NumericScale = 0.

>> Thanks!

>.



Mon, 28 Nov 2005 02:38:26 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Setting Decimal Places for a numeric column

2. Outlook form is moving the decimal place for a numeric field

3. How To Insert Column With Decimal Places

4. Please help - howto set the precision and numeric scale for a decimal type parameter

5. Numeric String To Decimal

6. format for fixed decimal places

7. Setting Decimal places

8. Currency with only 4 decimal places !!!

9. Decimal places

10. Rounding to two decimal places (Access 97)

11. Setting decimal places on reports using vba

12. Fixed decimal places in VB code

 

 
Powered by phpBB® Forum Software