Setting Decimals with CREATE TABLE statement

[Bezzie]

I have the following statement:

'create a temporary table in ADO_UserData called IHGridSheet
mySQL = "CREATE TABLE IHGridSheet (" & _
        "IHDay VarChar(6) NOT NULL, " & _
        "IDate DATE, " & _
        "IActualWeight SINGLE, " & _
        "IExpectedWeight SINGLE, " & _
        "IWeightLoss SINGLE, " & _
        "IDeviation SINGLE, " & _
        "HDate DATE, " & _
        "HActualWeight SINGLE, " & _
        "HExpectedWeight SINGLE, " & _
        "HWeightGain SINGLE, " & _
        "HDeviation SINGLE)"


ADO_UserData.Execute mySQL

to create a table in my database (VB6, Access 2000 table and MS Jet 4.0). This works to create the table but...

How do I set the decimals for the SINGLE fields? I want to set 2 decimals. After creating the table with the above statement it is on Auto.

[jggtz]

You could let that way and control the decimals places in the application side

JG

[Bezzie]

Could do that but then I still don't know how it is done. Could probably also use a currency field but I want to know how it's done. Previously only needed integer, text and currency field types but now getting into floating point values.

[jggtz]

Never tried with Access but in SQL Server the syntax to create a table and a numeric field with 2 decimals is :

CREATE TABLE [dbo].[TableName]
  ( [NumFieldName] [numeric] (13, 2) )

JG

[jggtz]

Try :

CREATE TABLE Test (dec_col DECIMAL(10, 2) NOT NULL)

JG

[Bezzie]

I tried DECIMAL. It adds two 'fields' to the number data type options of the Access table;
1. Precision
2. Scale
but Decimal Places stays on Auto.

[jggtz]

AFAIK changeing the Decimal Places, affects only the way the field is displayed not the way the field is stored... so the best, IMO, is as I posted in post #2 : You could let that way and control the decimals places in the application side

JG