By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 614 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

Make table in vb and set decimal scale

100+
P: 365
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.

Expand|Select|Wrap|Line Numbers
  1. Dim tdef As TableDef
  2. Set tdef = CurrentDb.CreateTableDef("tblReports")
  3. With tdef
  4.     .Fields.Append .CreateField("StaffID", dbInteger)
  5.     CurrentDb.TableDefs.Append tdef
  6. End With
  7.  
TIA for any clues

Dan
Feb 27 '09 #1
Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,623
@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:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim tdef As DAO.TableDef
  3. Dim fld As DAO.Field
  4.  
  5. Set MyDB = CurrentDb
  6. Set tdef = CurrentDb.CreateTableDef("tblReports")
  7. Set fld = tdef.CreateField("StaffID", dbInteger)
  8.  
  9. With fld
  10.   .Required = True
  11.   .DefaultValue = 9999
  12.   .OrdinalPosition = 1
  13. End With
  14.  
  15. tdef.Fields.Append fld
  16. MyDB.TableDefs.Append tdef
  17.  
  18. RefreshDatabaseWindow
Feb 27 '09 #2

Expert Mod 2.5K+
P: 2,545
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
Feb 27 '09 #3

100+
P: 365
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

Expand|Select|Wrap|Line Numbers
  1. Dim tdef As TableDef
  2. Set tdef = CurrentDb.CreateTableDef(LogStaffID & "tblReports")
  3. With tdef
  4.     .Fields.Append .CreateField("StaffID", dbInteger)
  5.     .Fields.Append .CreateField("Name", dbText)
  6.     .Fields.Append .CreateField("Date", dbDate)
  7.     .Fields.Append .CreateField("Comments", dbText)
  8.     .Fields.Append .CreateField("HoursPW", dbInteger) 'as a decimal to 1dp
  9.     .Fields.Append .CreateField("StartDate", dbDate)
  10.     .Fields.Append .CreateField("HolDays", dbInteger) 'as a decimal to 1dp
  11.     .Fields.Append .CreateField("SickDays", dbInteger) 'as a decimal to 1dp
  12.     CurrentDb.TableDefs.Append tdef
  13. End With
  14.  
Feb 27 '09 #4

Expert Mod 2.5K+
P: 2,545
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
Feb 27 '09 #5

100+
P: 365
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
Feb 27 '09 #6

Expert Mod 2.5K+
P: 2,545
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
Feb 27 '09 #7

100+
P: 365
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
Feb 27 '09 #8

ADezii
Expert 5K+
P: 8,623
@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.
Feb 28 '09 #9

ADezii
Expert 5K+
P: 8,623
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "ALTER TABLE tblReports ADD COLUMN StaffID DECIMAL (28,1);"
  4. CurrentProject.Connection.Execute strSQL
Feb 28 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.