Table decimal places

[Hank]

Hello,

I'm running Access 2000. I would like to know if its
possible to use VBA code to determine how many decimal places are
specified for each table field.

Currently I loop throught the Tables Collection, the Tables
Fields Collection, and then the Field Properties Collection but I do
not see what I need. I don't want to change the setting. I just want
to make a list of the decimal place setting for each "Single" field in
all Tables.

Thanks,
Hank Reed

[RoyVidar]

"Hank" <ha********@aol.comwrote in message
<11**********************@h48g2000cwc.googlegroups .com>:

Hello,

I'm running Access 2000. I would like to know if its
possible to use VBA code to determine how many decimal places are
specified for each table field.

Currently I loop throught the Tables Collection, the Tables
Fields Collection, and then the Field Properties Collection but I do
not see what I need. I don't want to change the setting. I just want
to make a list of the decimal place setting for each "Single" field
in all Tables.

Thanks,
Hank Reed

Doesn't the DecimalPlaces property give that?

Dim td As DAO.TableDef
Dim db As DAO.Database
Dim fd As DAO.Field
Set db = DBEngine(0)(0)
For Each td In db.TableDefs
For Each fd In td.Fields
If fd.Type = dbSingle Then
Debug.Print td.Name, fd.Name, _
fd.Properties("DecimalPlaces").Value
End If
Next fd
Next td

--
Roy-Vidar

[Hank]

Doesn't the DecimalPlaces property give that?

>
Dim td As DAO.TableDef
Dim db As DAO.Database
Dim fd As DAO.Field
Set db = DBEngine(0)(0)
For Each td In db.TableDefs
For Each fd In td.Fields
If fd.Type = dbSingle Then
Debug.Print td.Name, fd.Name, _
fd.Properties("DecimalPlaces").Value
End If
Next fd
Next td

--
Roy-Vidar

Roy,
I tried your function and got "Property Not Found" for the
"DecimalPlaces" property. Actually it didn't recognize "Caption" or
other properties I thought should be there. I tried listing the
properties and found out that "DecimalPlaces" was number 23 but could
not get the value. I tried using fd.Properties(1) - (25) and got
values up to (8) but it would not recognize (9) and up.
As a test, I added a new table with singles in it and your
function reads it just fine. I tried to characterize the other tables
to see what the difference was but I could not find anything
significant.
Any ideas?
Hank

[RoyVidar]

"Hank" <ha********@aol.comwrote in message
<11*********************@i3g2000cwc.googlegroups.c om>:

>Doesn't the DecimalPlaces property give that?

Dim td As DAO.TableDef
Dim db As DAO.Database
Dim fd As DAO.Field
Set db = DBEngine(0)(0)
For Each td In db.TableDefs
For Each fd In td.Fields
If fd.Type = dbSingle Then
Debug.Print td.Name, fd.Name, _
fd.Properties("DecimalPlaces").Value
End If
Next fd
Next td

--
Roy-Vidar

Roy,
I tried your function and got "Property Not Found" for the
"DecimalPlaces" property. Actually it didn't recognize "Caption" or
other properties I thought should be there. I tried listing the
properties and found out that "DecimalPlaces" was number 23 but could
not get the value. I tried using fd.Properties(1) - (25) and got
values up to (8) but it would not recognize (9) and up.
As a test, I added a new table with singles in it and your
function reads it just fine. I tried to characterize the other
tables to see what the difference was but I could not find anything
significant.
Any ideas?
Hank

Some properties, like the Caption property, doesn't exist until they
are
set or created, either through the interface, or programatically. So,
those fields where you haven't set any caption, will return a 3270 -
property not found error.

For the Decimal Places property, it is a bit different. I think that if
you create the table through design view in Access, the property is set
regardless of whether you actually alter the value there or not. If you
on the other hand create the table through DDL, then it's not.

I don't work with these properties much programatically, but I think
that if your purpose is to just list the property values, I think that
you can safely assume that 3270 for the caption property, means no
caption is set, and for Decimal Places property for double/single
fields, then it's the default "Auto".

If you use on error resume next, and check for err.number = 3270, then
you should be able to determine which properties exists or not.

--
Roy-Vidar

[Hank]

Some properties, like the Caption property, doesn't exist until they

are
set or created, either through the interface, or programatically. So,
those fields where you haven't set any caption, will return a 3270 -
property not found error.

For the Decimal Places property, it is a bit different. I think that if
you create the table through design view in Access, the property is set
regardless of whether you actually alter the value there or not. If you
on the other hand create the table through DDL, then it's not.

I don't work with these properties much programatically, but I think
that if your purpose is to just list the property values, I think that
you can safely assume that 3270 for the caption property, means no
caption is set, and for Decimal Places property for double/single
fields, then it's the default "Auto".

If you use on error resume next, and check for err.number = 3270, then
you should be able to determine which properties exists or not.

--
Roy-Vidar

Roy,
The Caption field wasn't really important to me. I was just
casting about to find anything that could be read. This exercise is
part of my migration to a Postgres database and I wanted a list of
Decimal Places on the Access side so the Postgres tables could be
created properly.
When I was having the problem reading the decimal places, I
was inadvertently linked to the Postgres tables (Dumb). Everything
works now.
Thanks for staying with this.
Hank