Avatar of Bill_388
Bill_388 asked on

How to get properties of an access decimal field using code

Using vba, how do we get the precision and scale properties of a field of an access table?  There is no problem when we try to get the other field properties like type or size.  Precision and scale properties apply to a decimal type number field.
CurrentDb.TableDefs(i).Fields(j).Precision will give an error

Open in new window

Microsoft DevelopmentMicrosoft Access

Avatar of undefined
Last Comment
MikeToole

8/22/2022 - Mon
ASKER
Bill_388

I am increasing the point value of my question to 250.  Please somebody answer my question.
ASKER
Bill_388

The purpose of being able to get the said properties thru code is to be able to automate the creation of an access database tabledefs documentation.  Thanks.
p.s. the point value is now maxed at 500.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Hold yer horses ....

mx
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jim P.

Try properties 24.
Jim P.


i=24:Debug.Print I & ": " & CurrentDb().TableDefs("Price_Table").Fields("Price").Properties(I).name & ": " & CurrentDb().TableDefs("Price_Table").Fields("Price").Properties(I).Value

Open in new window

Jeffrey Coachman


The "precision" of a numbe is based on it's "Size" property
From the help files:
Setting, Description, Decimal precision, Size
Byte, Stores numbers from 0 to 255 (no fractions)., None, 1 byte
Decima,Stores numbers from 10^281 through 10^281 (.mdb), 28, 12bytes
Integer, Stores numbers from 32,768 to 32,767 (no fractions)., None, 2 bytes
Long Integer, (Default) Stores numbers from 2,147,483,648 to 2,147,483,647 (no fractions)., None, 4 bytes
Single, Stores numbers from 3.402823E38 to 1.401298E45 for negative values and from
1.401298E45 to 3.402823E38 for positive values., 7, 4 bytes
Double, Stores numbers from1.79769313486231E308 to 4.94065645841247E324
for negative values and from 4.94065645841247E324 to 1.79769313486231E308 for positive values., 15, 8 bytes
(The "Format" of the number is something totally different though...)
Can you clarify what you mean by "Scale"?
 
JeffCoachman

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Public Function mPrp() As Boolean

Dim x
For x = 1 To 100
    On Error Resume Next
    Debug.Print x & "    " & CurrentDb.TableDefs("000tblA").Fields("DecField").Properties(x)
   
Next x
End Function

Precision is property 2
Can't find Scale yet.

mx
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Precision seems to be called CollatingOrder - but is returning the Precision I set (and have changed).

Here is the list ... but 23 (decimal places) is not returning the correct number

0    Value
1    Attributes
2    CollatingOrder
3    Type
4    Name
5    OrdinalPosition
6    Size
7    SourceField
8    SourceTable
9    ValidateOnSet
10    DataUpdatable
11    ForeignName
12    DefaultValue
13    ValidationRule
14    ValidationText
15    Required
16    AllowZeroLength
17    FieldSize
18    OriginalValue
19    VisibleValue
20    ColumnWidth
21    ColumnOrder
22    ColumnHidden
23    DecimalPlaces
24    DisplayControl
Jim P.

It appears to be either 23 or 24 depending on something else in a the field properties.

The code below dumps properties names  to a table called Table_Field_Properties with the text fields -- TableName, FieldName, PropertyName, RefNum.

Do this on a small database. ;-)
Public Function List_Table_Params()
 
Dim I As Integer
Dim J As Integer
Dim K As Integer
Dim SQL As String
 
DoCmd.SetWarnings False
For I = 0 To CurrentDb().TableDefs.count - 1
    'Debug.Print I & ": " & CurrentDb().TableDefs(I).NAME
    For J = 0 To CurrentDb().TableDefs(I).Fields.count - 1
        'Debug.Print J & ": " & CurrentDb().TableDefs(I).Fields(J).NAME
        For K = 0 To CurrentDb().TableDefs(I).Fields(J).Properties.count - 1
            SQL = "INSERT INTO Table_Field_Properties (TableName, FieldName, PropertyName, RefNum) " & _
                "VALUES('" & CurrentDb().TableDefs(I).NAME & "', '" & _
                 CurrentDb().TableDefs(I).Fields(J).NAME & "', '" & _
                 CurrentDb().TableDefs(I).Fields(J).Properties(K).NAME & "', '" & _
                 CStr(I) & ":" & CStr(J) & ":" & CStr(K) & "')"
            DoCmd.RunSQL SQL
            Debug.Print CStr(I) & ":" & CStr(J) & ":" & CStr(K)
        Next K
    Next J
Next I
 
End Function

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"It appears to be either 23 or 24 depending on something else in a the field properties."

Which property ?

mx
aikimark

You base the precision on the type of numeric field.  Single is 4 bytes and Double is 8 bytes.  These are both IEEE floating point field definitions.

For Long (4 bytes) and Integer (2 bytes) there is no 'precision'
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Not for the Decimal data type.
There is a Precision and Scale property.

mx
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

see attached GIF
Decimal.gif
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

And hey ... how about that Binary data type.  Lets see you select that from the list :-)

mx
Jeffrey Coachman

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Jeff ... he is trying to find the value of the property via code.  

mx
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Bill_388:

You were in a rush to get answer, but have not posted back in several hours.  What's up with that?

mx
Jeffrey Coachman

MX,

"Jeff ... he is trying to find the value of the property via code. " 

Yeah, but if it is not working, (and Bill_388 is really in a rush), then perhaps what we have presented thus far is as good as it will get.

Jeff
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

<just got back from a ripping blues jam>

"Yeah, but if it is not working,"

What's not working ?
I have the Precision - it's prop # 2 - even though it's called Collating Order.  Just need Scale now.  I have a feeling it's one you have to create  - even though you 'see' it on the prop sheet ... kinda link Description.

mx
Jeffrey Coachman

What's not working is that no one seems to know how to get this "scale" info via VBA.

Which is what the Bill_388 asked for.

So if Bill_388 is really in a rush, ...then, again,  this is perhaps as good as it will get.

Jeff

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Rush, right ... oh well.

mx
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
MikeToole

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
aikimark

maybe
1. precision is an artificial presentation/behavior pseudo-property that really isn't part of the tabledef.  You can't define the data structure with this.

2. this 'property' is stored in the MsysObjects table.

3. this field property was added after the DAO spec was 'functionally stabilized'.
Jim P.

Well the code below will work with the table in http:/Q_24088069.html#23564838 by adding a PropertyValue column.

I  restricted it to columns that had a DecimalPlaces Property Name. The values I got back for CollatingOrder were

PropertyName      PropertyValue
CollatingOrder              10
CollatingOrder          1024
CollatingOrder          1033
CollatingOrder              18
CollatingOrder                8
Public Function Get_Field_Param_Values()
 
Dim DB As Database
Dim RS As Recordset
Dim SQL As String
 
SQL = "SELECT * " & _
    "FROM Table_Field_Properties " & _
    "WHERE FieldName IN (SELECT FieldName " & _
                    "FROM Table_Field_Properties " & _
                    "WHERE PropertyName = 'DecimalPlaces')"
    
 
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("Table_Field_Properties")
 
RS.MoveFirst
 
On Error Resume Next
 
Do Until RS.EOF = True
    With RS
        .Edit
        !PropertyValue = CurrentDb().TableDefs(!TableName).Fields(!FieldName).Properties(!PropertyName).Value
        .Update
    End With
    Debug.Print RS!RefNum
    RS.MoveNext
Loop
 
Set RS = Nothing
Set DB = Nothing
 
End Function

Open in new window

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"precision is an artificial presentation/behavior pseudo-property that really isn't part of the tabledef."

It appears that it is ...

CurrentDb.TableDefs("000tblA").Fields("DecField").Properties(2)

is returning the value I keep setting and changing (to test) ...

mx
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
aikimark

@mx

Thanks.  I learned something about DAO today.  I wonder why they call it CollatingOrder instead of precision?
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Well ... something is WEIRD here for sure.  CollatingOrder ??? Right.  What is that?

Double check me on this.
Create a table with a Decimal type
Set the Precision to some different values
In the immediate window ... execute

CurrentDb.TableDefs("YourTable").Fields("YourField").Properties(2)

and see what you get ...

mx
aikimark

I get the value that is labeled "PRECISION" in the table design window.

What do you get when you display the following in the immediate window?

CurrentDb.TableDefs("YourTable").Fields("YourField").Properties(2).Name
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"I get the value that is labeled "PRECISION" in the table design window."

Yes ... Precision and Scale are there.

What do you get when you display the following in the immediate window?

CollatingOrder ... which is what I was saying before ... BUT ... that returns the Precision value.

Is that what you get?
Jim P.

Try this on -- it is the DecimalPlaces property as long as you don't leave it on Auto.

I created a special table of nothing but a Long, an Int and singles. results speak for themselves.
Q-24088069-Decimal-Places.xls
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

I tried 0 and 2 for Decimal Places ... and still CollatingOrder ...

?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Bill_388

DatabaseMX, Jimpen, boaq2000, aikimark,MikeToole,

Thank you very much to all of you for your time in answering my question.  This question was a neglected question for some time now until it was put into the access database zone.  I appreciate your help.  Database MX has hit the answer to the precision property by pointing to the CollatingOrder as actually being the Precision property.  However, the scale property is still a question mark.  Is it a property of another property?

Bill_388
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"However, the scale property is still a question mark."

 I have a feeling it's one you have to create (before you can reference it)  - even though you 'SEE' it on the prop sheet ... kinda like the Description  property of a table.

mx
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

What about what Mike said @  http:#a23568496 ?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
MikeToole

The way I read the situation was mentioned in a previous post - and that is that the Decimal Type was intoduced when DAO had already been sidelined as a dead-end, so the new properties never got implemented.
ADO was the future, so the properties are supported by ADOX and can be accessed in the way I suggested.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"when DAO had already been sidelined as a dead-end .... ADO was the future"

That's what M$ thought VIsta was too (the future), lol. XP Still rules.  Long Live DAO!

mx
MikeToole

With the advent of Access 2007, DAO now has a star part in "Return from the Dead" :-)
(Though it still doesn't do it with Precision and Scale)

MikeT
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"Return from the Dead"

LOL!!!!  That's good Mike ... I like it!!!

I'm betting there IS a way to get the Scale.

And remember, Access 14 is in the works.   Damn good thing the acronym was not ... DOA !!!

mx
ASKER
Bill_388

Hi MikeToole, DatabaseMX
I tried out Mike's solution using ADO and it worked fine.  I would however, credit DatabaseMX for providing the DAO solution for the Precision.  I would like to suggest 250 points each for MikeToole and DatabaseMX.  Thank You Very Much to everyone.

Bill_388

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

??
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
MikeToole

A full working answer should get an A grade, mais c'est la vie
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

I clearly showed how to get the Precision value.

I'm sure there is a way to get the Scale also ...

mx
MikeToole

That's fine by me.
Cheers,
Mike
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.