How to get properties of an access decimal field using code

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

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.

Hold yer horses ....

mx

Try properties 24.

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

Select allOpen in new window

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

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

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

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

Select allOpen in new window

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

Which property ?

mx

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'

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

mx

see attached GIF
Decimal.gif

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

mx

Again, from the help file, the max. Decimal Precision of a Decimal Field size is 28.
There is nothing in the help files (Click the Scale prop. and press F1) on the "Scale"

I did find this though:
http://books.google.com/books?id=D1mRmhtoFyoC&pg=PA169&lpg=PA169&dq=scale+property+decimal+data+type&source=web&ots=Av2r967Bod&sig=_5KPvRP1Kn6HOHwgi7Ryymzjyd4&hl=en&ei=J7uLSbmSO9WDtweqrqWqCw&sa=X&oi=book_result&resnum=8&ct=result

Isn't the Decimal Data type still a bad choice because of all the issues it has?
ex:
http://books.google.com/books?id=gYVi5RDPACcC&pg=PA107&lpg=PA107&dq=scale+property+decimal+data+type&source=bl&ots=Xy9kvFux3M&sig=sX8_tlPAa7QUouTTL85CM5Y1F8s&hl=en&ei=J7uLSbmSO9WDtweqrqWqCw&sa=X&oi=book_result&resnum=4&ct=result#PPA107,M1

JeffCoachman

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

mx

Bill_388:

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

mx

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

<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

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

Rush, right ... oh well.

mx

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'.

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

Select allOpen in new window

"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

@mx

Thanks.  I learned something about DAO today.  I wonder why they call it CollatingOrder instead of precision?

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

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

"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?

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

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

?

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

"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

What about what Mike said @  http:#a23568496 ?

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.

"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

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

"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

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

??

A full working answer should get an A grade, mais c'est la vie

I clearly showed how to get the Precision value.

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

mx

That's fine by me.
Cheers,
Mike