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

Table decimal places

P: n/a
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

Aug 18 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"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
Aug 18 '06 #2

P: n/a
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

Aug 20 '06 #3

P: n/a
"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
Aug 20 '06 #4

P: n/a
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

Aug 21 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.