Make table in vb and set decimal scale

[Dan2kx]

Good Aft,

Trying to use the code below to make a table in vb, but i can't figure out how to set the scale, which needs to be one in this scenario.

Dim tdef As TableDef
Set tdef = CurrentDb.CreateTableDef("tblReports")
With tdef
    .Fields.Append .CreateField("StaffID", dbInteger)
    CurrentDb.TableDefs.Append tdef
End With
 

TIA for any clues

Dan

[ADezii]

@Dan2kx
I'm a little confused, Dan2kx, since Scale would hardly be applicable to an Integer Field. Aside from this, to dynamically set Field Properties when they are created along with a TableDef Object, you can set them prior to the Field being Appended to the Fields Collection of the TableDef Object as well as before the TableDef is Appended to the TableDefs Collection of the Database Object, as in:

Dim MyDB As DAO.Database
Dim tdef As DAO.TableDef
Dim fld As DAO.Field
 
Set MyDB = CurrentDb
Set tdef = CurrentDb.CreateTableDef("tblReports")
Set fld = tdef.CreateField("StaffID", dbInteger)
 
With fld
  .Required = True
  .DefaultValue = 9999
  .OrdinalPosition = 1
End With
 
tdef.Fields.Append fld
MyDB.TableDefs.Append tdef
 
RefreshDatabaseWindow

[Stewart Ross]

Dan, I'm not sure what you are referring to when you say 'scale' and 'one'. What property or properties of a tabledef are you talking about?

-Stewart

[Dan2kx]

well i tried dbDecimal but it said i had an invalid field data type.

i will give you my current code, i want the 3 fields highlighted to be decimal values

Dim tdef As TableDef
Set tdef = CurrentDb.CreateTableDef(LogStaffID & "tblReports")
With tdef
    .Fields.Append .CreateField("StaffID", dbInteger)
    .Fields.Append .CreateField("Name", dbText)
    .Fields.Append .CreateField("Date", dbDate)
    .Fields.Append .CreateField("Comments", dbText)
    .Fields.Append .CreateField("HoursPW", dbInteger) 'as a decimal to 1dp
    .Fields.Append .CreateField("StartDate", dbDate)
    .Fields.Append .CreateField("HolDays", dbInteger) 'as a decimal to 1dp
    .Fields.Append .CreateField("SickDays", dbInteger) 'as a decimal to 1dp
    CurrentDb.TableDefs.Append tdef
End With
 

[Stewart Ross]

Dan, there is no need to specify the number of decimal places - this is a formatting issue about what you display, not the type of the field itself.

In the fields collection there is no format property available to set directly in VBA code. I reckon (although I haven't checked this for sure) that the one provided by Access in the Tables design view is actually a custom property added on the fly to the Properties collection of the fields concerned (like the description property of a table or query - which is such a custom property). If you really wanted to create a default format for your fields specifying display to 1 decimal place you'd have to append the custom format property to the field's properties collection in your code. Personally, I reckon this is a waste of time, as any formatting to 1 decimal place should be done in the texboxes of the forms or reports you subsequently generate, not in the field definition itself.

Bear in mind that the formatting has nothing at all to do with the precision of the value, which is an inherent property of the numeric type itself.

-Stewart

[Dan2kx]

well the reason i require a decimal is to display this in a report, normally as either a whole number or a .5 value (not always), should i store this information as text then?! the table is populated purely for the purpose of this report.

Dan

[Stewart Ross]

No!! Text is entirely the wrong solution! Use a double instead.

A double precision numeric value is internally accurate to 15 significant digits - define the field as double and format it to one decimal place later in whatever textbox you use for display or reporting purposes.

By the way, I could not get the decimal type recognised in the createfield for me either - but this type's 28 places of precision is surely not needed for your current application!

-Stewart

[Dan2kx]

Well that seems to work quite well, it shows in 1dp with no extra fromatting,

thank you very much...

just curious, why is there no ability to create a decimal field then!?

dan

[ADezii]

@Dan2kx
I think the answer to your question is that the Decimal data type can only be used within the context of a Variant, there is no Decimal Data Type per say. You cannot declare a Variable to be of type Decimal. You can, however, create a Variant whose subtype is Decimal using the CDec() function. You can set the Field Size Property of a Number Field in a Table to Decimal, so I'm assuming that there is some kind of Implicit Data Type Conversion going on here.

[ADezii]

Just a little useless information. The following DDL will add a Field named StaffID to the Table tblReports. This Field will be of the DECIMAL Data Type and have a Precision of 28 and Scale of 1:

Dim strSQL As String
 
strSQL = "ALTER TABLE tblReports ADD COLUMN StaffID DECIMAL (28,1);"
CurrentProject.Connection.Execute strSQL