How to get the decimal places property from tabledef

  • Thread starter Thread starter Packey
  • Start date Start date
P

Packey

I am trying to loop through the fields collection of a
tabledef to get the field size and decimal positions of a
field that is of type decimal. I have not been able to
find a property in the fields collection that gives me
those values. When you go into design view of the table
the field size and decimal positions are displayed for
fields that are of the type decimal. Where does that
information come from and how to I get access to in in VBA?

Thanks
 
Packey,
Regarding:
to get the field size and decimal positions of a
field that is of type decimal.
Click to expand...

There is no field type decimal.
I'm sure you mean a Number datatype of
Single, Double, or Currency.

See if you can use something here:

Place this Sub Procedure in a module.
Then run it.

Public Sub EnumerateTableProperties()

On Error GoTo Err_Handler
Dim db As Database
Dim tbl As TableDef
Dim P As Property
Dim fld As Field
Set db = CurrentDb
For Each tbl In db.TableDefs

If tbl.Name = "YOUR TABLE NAME HERE" Then
Debug.Print tbl.Name
Debug.Print "Number of Fields in table " & tbl.Fields.count
For Each fld In tbl.Fields

If fld.Name = "FIELD NAME HERE" Then ' ** Optional Limit to one field
For Each P In fld.Properties
Debug.Print fld.Name, P.Name, P.Value
Next P
End If ' ** Optional Limit to one field

Next fld
End If
Next tbl

Exit_Sub:
Set db = Nothing
Exit Sub

Err_Handler:
Resume Next

End Sub

===
Check your Debug Window.
The above code will return this data for a selected Number
field named "Newfield" in a table named "tblBasicdata".

tblBasicData
Number of Fields in table 47
Newfield Attributes 1
Newfield CollatingOrder 1033
Newfield Type 5
Newfield Name Newfield
Newfield OrdinalPosition 19
Newfield Size 8
Newfield SourceField Newfield
Newfield SourceTable tblBasicData
Newfield DataUpdatable False
Newfield DefaultValue
Newfield ValidationRule
Newfield ValidationText
Newfield Required False
Newfield AllowZeroLength False
Newfield ColumnWidth -1
Newfield ColumnOrder 0
Newfield ColumnHidden False
Newfield Format Fixed
Newfield DecimalPlaces 4

Remove the If statement marked with ** to show all the
fields. In a table with many fields, this may exceed the
display capabilities of the Debug Window.
 
Back
Top