Syntax for adding a decimal field to a table (1 Viewer)

[Alc]

I've tried the following, but each gives me a 'syntax error in field definition' message.

CurrentDb.Execute "ALTER TABLE [AEO32B] ADD COLUMN Severity3 DECIMAL(10,3)", dbFailOnError

CurrentDb.Execute "ALTER TABLE [AEO32B] ADD COLUMN Severity3 NUMBER(10,3)", dbFailOnError

CurrentDb.Execute "ALTER TABLE [AEO32B] ADD COLUMN Severity3 NUMERIC(10,3)", dbFailOnError

Adding currency and text fields were no problem, but I can't seem to create a decimal.

 

[Minty]

Assuming it's a Access table try one from the following list

Microsoft Access data type        Data type (CREATE TABLE) ODBC SQL data type 
~~~~~~~~~~~~~~~~~~~~~~~~~~        ~~~~~~~~~~~~~~~~~~~~~~~  ~~~~~~~~~~~~~~~~~~ 
BIGBINARY[1]                      LONGBINARY               SQL_LONGVARBINARY   
BINARY                            BINARY                   SQL_BINARY         
BIT                               BIT                      SQL_BIT             
COUNTER                           COUNTER                  SQL_INTEGER         
CURRENCY                          CURRENCY                 SQL_NUMERIC         
DATE/TIME                         DATETIME                 SQL_TIMESTAMP       
GUID                              GUID                     SQL_GUID           
LONG BINARY                       LONGBINARY               SQL_LONGVARBINARY   
LONG TEXT                         LONGTEXT                 SQL_LONGVARCHAR[2] 
MEMO                              LONGTEXT                 SQL_LONGVARCHAR[2] 
NUMBER (FieldSize= SINGLE)        SINGLE                   SQL_REAL           
NUMBER (FieldSize= DOUBLE)        DOUBLE                   SQL_DOUBLE         
NUMBER (FieldSize= BYTE)          UNSIGNED BYTE            SQL_TINYINT         
NUMBER (FieldSize= INTEGER)       SHORT                    SQL_SMALLINT       
NUMBER (FieldSize= LONG INTEGER)  LONG                     SQL_INTEGER         
NUMERIC                           NUMERIC                  SQL_NUMERIC         
OLE                               LONGBINARY               SQL_LONGVARBINARY   
TEXT                              VARCHAR                  SQL_VARCHAR[1]     
ARBINARY                          VARBINARY                SQL_VARBINARY

 

[Alc]

Assuming it's a Access table try one from the following list

Microsoft Access data type        Data type (CREATE TABLE) ODBC SQL data type
~~~~~~~~~~~~~~~~~~~~~~~~~~        ~~~~~~~~~~~~~~~~~~~~~~~  ~~~~~~~~~~~~~~~~~~
BIGBINARY[1]                      LONGBINARY               SQL_LONGVARBINARY  
BINARY                            BINARY                   SQL_BINARY        
BIT                               BIT                      SQL_BIT            
COUNTER                           COUNTER                  SQL_INTEGER        
CURRENCY                          CURRENCY                 SQL_NUMERIC        
DATE/TIME                         DATETIME                 SQL_TIMESTAMP      
GUID                              GUID                     SQL_GUID          
LONG BINARY                       LONGBINARY               SQL_LONGVARBINARY  
LONG TEXT                         LONGTEXT                 SQL_LONGVARCHAR[2]
MEMO                              LONGTEXT                 SQL_LONGVARCHAR[2]
NUMBER (FieldSize= SINGLE)        SINGLE                   SQL_REAL          
NUMBER (FieldSize= DOUBLE)        DOUBLE                   SQL_DOUBLE        
NUMBER (FieldSize= BYTE)          UNSIGNED BYTE            SQL_TINYINT        
NUMBER (FieldSize= INTEGER)       SHORT                    SQL_SMALLINT      
NUMBER (FieldSize= LONG INTEGER)  LONG                     SQL_INTEGER        
NUMERIC                           NUMERIC                  SQL_NUMERIC        
OLE                               LONGBINARY               SQL_LONGVARBINARY  
TEXT                              VARCHAR                  SQL_VARCHAR[1]    
ARBINARY                          VARBINARY                SQL_VARBINARY

Thanks very much.
May be a stupid question, but if I were doing it manually I could select Decimal as a data type. Is that not available via VBA?

 

[Minty]

It's a good question. I don't know in VBA, as most of my development is done in SQL / Azure tables these days.

 

[Alc]

It's a good question. I don't know in VBA, as most of my development is done in SQL / Azure tables these days.

Thanks again.

I can get by using Double, but it was bugging me why I could choose Decimal from the drop-down list of number types in design view but can't work out how to use it via VBA. Oh well, no time to puzzle it out now.

 

[theDBguy]

Thanks again.

I can get by using Double, but it was bugging me why I could choose Decimal from the drop-down list of number types in design view but can't work out how to use it via VBA. Oh well, no time to puzzle it out now.

Hi. According to this article, DECIMAL is not available through DDL, nor is it available through DAO, which is what I would normally use.

Microsoft Access Field Data Type Reference | bytecomb

 

[The_Doc_Man]

I might suggest that if you think DOUBLE will work for you, use that and just format the OUTPUT to "##########.###" or whatever is needed. I have found some articles in the past where the DECIMAL field type is a troublemaker in Access.

 

[sonic8]

I've tried the following, but each gives me a 'syntax error in field definition' message.

CurrentDb.Execute "ALTER TABLE [AEO32B] ADD COLUMN Severity3 DECIMAL(10,3)", dbFailOnError

Some enhancements in Access data structures are only accessible via DDL when using ADO instead of DAO.
You can use this:
CurrentProject.Connection.Execute "ALTER TABLE [AEO32B] ADD COLUMN Severity3 DECIMAL(10,3)"

 

[arnelgp]

good googling!

Some enhancements in Access data structures

you and minty has same source:
Data type equivalents: MS Access Tables ↔ 'CREATE TABLE' Queries ↔ ODBC SQL - Stack Overflow

 

[sonic8]

you and minty has same source:

No