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
9 7168 @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
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
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
-
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
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
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
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
@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.
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Peter Blatt |
last post by:
Does 5 represent the total numer of digits (including the fractional portion) or only the number of places
BEFORE the decimal point? Moreover does the number include the decimal point?
Are there...
|
by: Stephen Patten |
last post by:
Hi All,
While in the process of building my table (40 or so Insert statments)
can I then query ("select * from @Table_variable") and use the results
up to theat point for another insert into...
|
by: italia |
last post by:
I changed the Fieldsize Property from text to Long Integer and Decimal
Places = 6.
I had decimals in the original field. But after the transfer, the
digits after the decimals are gone.
Now...
|
by: Michel Esber |
last post by:
Hello,
DB2 V8 FP 11 running on Linux.
Given two tables:
T_SW_ID (SW_ID INTEGER, SW_NAME VARCHAR);
T_SW (MACHINE_ID varchar, SW_ID DECIMAL (8), VERSION varchar,
Product_ID varchar)
|
by: Vamsi |
last post by:
Hi,
I have a column defined as decimal(4,3).
I get the following error when I try to insert "12.50000000000000"
CLI0111E Numeric value out of range. SQLSTATE=22003
sqlstate = 22003
I even...
|
by: uwcssa |
last post by:
I try to drop a table as:
I got: During SQL processing it returned:
SQL0478N The object type "TABLE" cannot be dropped because there is
an object "sch.SQL070515104729271", of type "FUNCTION",...
|
by: spanky1968 |
last post by:
Hello,
I have a small dilemma I need some help with. I would like to insert into a DB2 table the system date (timestamp) along with data retrieved via a db2 select statement but I am unsure how to...
|
by: Frank Millman |
last post by:
Hi all
I have a standard requirement for a 'decimal' type, to instantiate and
manipulate numeric data that is stored in a database. I came up with a
solution long before the introduction of the...
|
by: selvakumari |
last post by:
I am doing decimal conversion in my code as below
if datachNode.InnerText is "147.00" and scaleFactor.InnerText is "1"
decimal value = Convert.ToDecimal(datachNode.InnerText.ToString());...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |