Setting Decimals with CREATE TABLE statement

I have the following statement:

Code:

---

'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.

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

JG

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.

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

Code:

---

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

---

JG

Try :

Code:

---

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

---

JG

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.

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