Create Properties for Decimal and Date fields

[gaugust]

I am having a problem assigning properties to a date(ICUdt) and decimal field
(random). I would like to assign a format of "short date" to the date field
and a precision of 18, scale of 18 and decimal places of "auto" to the
decimal field. I tried to assign the "short date" to the date field but it is
not working. Could you please let me know what the syntax is to assign
properties to these two fields. Thanks. Here is my code:

Private Sub cmdCreatecases_Click()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim prp As DAO.Property
Dim fldControl As DAO.Field
Dim fldICUdt As DAO.Field
Dim fldRandom As DAO.Field
Set dbs = CurrentDb
On Error Resume Next

'Create a table for each case (record) in cases table
'Create the table definition in memory
tablename = "Control1"
Set tdf = dbs.CreateTableDef(tablename)


Set fldControl = tdf.CreateField("Control", dbInteger)
fldControl.Required = True

'Create the Primary Key index using Control
Set idx = tdf.CreateIndex("PrimaryKey")
idx.Primary = True
idx.Fields.Append tdf.CreateField("Control")
idx.Fields.Refresh

'Append the index and refresh
tdf.Indexes.Append idx
tdf.Indexes.Refresh

Set fldICUdt = tdf.CreateField("ICUdt_Con", dbDate)
fldICUdt.Required = False

Set fldRandom = tdf.CreateField("Random", dbDecimal)
fldRandom.Required = True

'Append the fields to the TableDef's Fields collection
tdf.Fields.Append fldControl
tdf.Fields.Append fldICUdt
tdf.Fields.Append fldRandom

'Create a property
Set prp = fldICUdt.CreateProperty("Format", dbText, "Short Date")
'Add the property to the field
fldICUdt.Properties.Append prp

'Append the TableDef to the Database's TableDefs collection
dbs.TableDefs.Append tdf

'Refresh the TableDefs collection
dbs.TableDefs.Refresh
Application.RefreshDatabaseWindow
Set fldControl = Nothing
Set fldICUdt = Nothing
Set fldRandom = Nothing
End Sub

 

[Marshall Barton]

I am having a problem assigning properties to a date(ICUdt) and decimal field
(random). I would like to assign a format of "short date" to the date field
and a precision of 18, scale of 18 and decimal places of "auto" to the
decimal field. I tried to assign the "short date" to the date field but it is
not working. Could you please let me know what the syntax is to assign
properties to these two fields. Thanks. Here is my code:

Private Sub cmdCreatecases_Click()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim prp As DAO.Property
Dim fldControl As DAO.Field
Dim fldICUdt As DAO.Field
Dim fldRandom As DAO.Field
Set dbs = CurrentDb
On Error Resume Next

'Create a table for each case (record) in cases table
'Create the table definition in memory
tablename = "Control1"
Set tdf = dbs.CreateTableDef(tablename)


Set fldControl = tdf.CreateField("Control", dbInteger)
fldControl.Required = True

'Create the Primary Key index using Control
Set idx = tdf.CreateIndex("PrimaryKey")
idx.Primary = True
idx.Fields.Append tdf.CreateField("Control")
idx.Fields.Refresh

'Append the index and refresh
tdf.Indexes.Append idx
tdf.Indexes.Refresh

Set fldICUdt = tdf.CreateField("ICUdt_Con", dbDate)
fldICUdt.Required = False

Set fldRandom = tdf.CreateField("Random", dbDecimal)
fldRandom.Required = True

'Append the fields to the TableDef's Fields collection
tdf.Fields.Append fldControl
tdf.Fields.Append fldICUdt
tdf.Fields.Append fldRandom

'Create a property
Set prp = fldICUdt.CreateProperty("Format", dbText, "Short Date")
'Add the property to the field
fldICUdt.Properties.Append prp

'Append the TableDef to the Database's TableDefs collection
dbs.TableDefs.Append tdf

'Refresh the TableDefs collection
dbs.TableDefs.Refresh
Application.RefreshDatabaseWindow
Set fldControl = Nothing
Set fldICUdt = Nothing
Set fldRandom = Nothing
End Sub

I believe that the table field must be appended to the table
def's Fields collection before you can create an index on
the field.

I don't think it applies to the Format property, but I know
there are some properties that must be appened to their
field object before you append the field to the table def.
I would expect this part of you code to work.

I haven't tried it, but I'm pretty sure that DAO does not
expose the Decimal type's specific properties. You might
try using ADOX for this purpose or find an alterative to the
only partially implemented Decimal type.

With that meager, inadequate and possibly inaccurate
response out of the way, I really have to wonder why you
care what the field's format is set to. Since it is
inadvisable to expose a table to users, no one should be too
upset if it displays in a default format. If you want it
for export to another program (e.g. Word, Excel), then why
not precreate a query that has the data formatted the way
you want it (probably something that is not dependent on a
user's date format settings in Windows.