VBA - How to get the number of decimal places for a table field

[AccessAllstars]

As part of my development process I create a table which holds all table names, field names, field type etc... I think of it as a data dictionary. I review this to confirm I have been consistent in naming conventions, field size, etc... I have everything working just fine with one exception, I cant seem to get the number of decimal places. This is all DAO
...
Dim daTdf As DAO.TableDef
Dim fld As DAO.Field
...

With db
tblCount = .TableDefs.Count
For Each daTdf In .TableDefs
'''Debug.Print daTdf.Name
Set td = daTdf
NameHold = td.Name
Select Case Left(NameHold, 4)
Case "DBA_", "MSys", "qwet", "tblf"
GoTo NextTableDef
End Select

On Error Resume Next
For Each fld In td.Fields
FieldDescription = fld.Name
TypeHold = FieldTypeName_DAO(fld)
SizeHold = fld.Size
DecimalPlacesHold = fld.DecimalPlaces ???????????
If fld.Required = False Then
FieldRequired = "FALSE"
Else
FieldRequired = "TRUE"
End If
...

Everything works great except for the decimal places. How can I get the number of decimal places?

Thank You!

 

[theDBguy]

Hi. Welcome to AWF!

Can't check it right now, but I can't remember if I include decimal places in my demo. Maybe you could check it out.

Get a List of Table Fields

 

[561414]

Did you try
.Fields(field).Properties("DecimalPlaces").Value

In your case, perhaps
DecimalPlacesHold = fld.Properties("DecimalPlaces").Value

edit: fixed typos and errors

 

[561414]

Or if you prefer fld.Properties!DecimalPlaces

This and much more can be found if you inspect your fld variable

 

[AccessAllstars]

Thank you DBGuy - your code does not get the decimal places. It did however give me an idea - check the fields properties. In my case, I need DecimalPlacesHold = fld.Properties("DecimalPlaces")

A few items to note - some you may know but this is for others who may read this post

1. the property names don't always match what you see in the table designer or what the online documentation says - in this case the space between Decimal and Places needed to be removed
2. Intellisense doesn't always give the correct name - example fld.FieldSize is available via intellisense but the value is actually fld.Size
3. If the value for the decimal places is AUTO the number 255 is returned
4. the number 255 is returned for non numeric fields

Thank you for sharing your demo/code.

 

[AccessAllstars]

Thank You 561414! I am not sure I follow how I can "inspect" the fld variables - there is no intellisense after the ! - what should I have done? I did list all the properties - but often you have a property that is not valid/available for that type. How does a text field have decimal places?

 

[theDBguy]

Thank you DBGuy - your code does not get the decimal places. It did however give me an idea - check the fields properties. In my case, I need DecimalPlacesHold = fld.Properties("DecimalPlaces")

A few items to note - some you may know but this is for others who may read this post

1. the property names don't always match what you see in the table designer or what the online documentation says - in this case the space between Decimal and Places needed to be removed
2. Intellisense doesn't always give the correct name - example fld.FieldSize is available via intellisense but the value is actually fld.Size
3. If the value for the decimal places is AUTO the number 255 is returned
4. the number 255 is returned for non numeric fields

Thank you for sharing your demo/code.

Glad to hear you got it sorted out. Good luck with your project.

 

[561414]

That's a great question. Before that, in order to inspect your variable you enter debug mode, just make sure your Locals window is open. This is how I did it for your case, just a simple Stop after initializing the rst variable puts it in the Locals window and I can expand it with the plus sign to know what members loaded with it.

Sub test()
    Dim rst As dao.Recordset
    Set rst = CurrentDb.OpenRecordset("somedata")
    Stop
    Debug.Print rst.Fields("wer").Properties("DecimalPlaces").Value
    Debug.Print rst!wer.Properties!DecimalPlaces
End Sub

I used a recent database, that's why the fields are different, but applicable.

Now, the table being tested there is very simple, it has an autonumber, a short text and a number type. All of them have a Type member in common, I don't have the full list at hand, but here's a few of them:
AutoNumber: 4,
Short Text: 10,
Long Text/Memo: 12,
Long Number: 4,
Double: 7, etc...

You can also create the list yourself if you don't find it online. Just make a table with the different data types you want and then check what Type it assigns it.

With that, you can anticipate the properties in code with a select case or if/else.

 

[561414]

You may find a few extra issues when you try to anticipate the properties of certain types. I found that an autonumber is type 4, just like a long type. However, the autonumber has no DecimalPlaces property while the Long type does, for some reason. But there are still possibilities we can test for in that case, such as the count of properties, an autonumber has 30 and a long type has 32. Or another member called "Attributes", if it's 49, it's an autonumber. You could also check if the property exists after all. Anyway, all of this can be found by inspecting your variables. Here's a little code snippet you can play with.

Sub test()
    Dim rst As dao.Recordset
    Set rst = CurrentDb.OpenRecordset("mytable")
    Dim fld As field
    Dim prp As Property
'    Stop
    For Each fld In rst.Fields
        Select Case fld.Type
        Case 4
            If fld.Attributes = 49 Then
                Debug.Print fld.Name, fld.Type, fld.Properties.Count, "autonumber has no decimals"
            Else
                Debug.Print fld.Name, fld.Type, fld.Properties.Count, "Decimals:" & fld.Properties!DecimalPlaces
            End If
        Case 10
            Debug.Print fld.Name, fld.Type, fld.Properties.Count, "I am short text"
        Case Else
            Debug.Print fld.Name, fld.Type, fld.Properties.Count
        End Select
    Next fld

End Sub

 

[561414]

I did a bunch of edits to my answers, sorry about that. I promise they'll stay as they are from now on.

 

[The_Doc_Man]

Let's not forget the Object Browser, available in the Database Tools >> VBA window in the code menu bar. If you know the name of the object, you can see its properties... with a word of warning that sometimes a property is listed but won't be present. For instance, an unbound textbox does not have a .OldValue even though that property IS listed for a textbox.

If you see an encoded item like an object type, Object Browser can show you the enumeration list for that thing - like text formats, as
acTextFormatHTMLRichText or acTextFormatPlain (found under AcTextFormat). And at the bottom if it is an enumeration thing, you can also see the numeric value for it, since if it has to be passed to SQL that can't see the definitions, you have the number anyway.

 

[561414]

Let's not forget the Object Browser, available in the Database Tools >> VBA window in the code menu bar. If you know the name of the object, you can see its properties... with a word of warning that sometimes a property is listed but won't be present. For instance, an unbound textbox does not have a .OldValue even though that property IS listed for a textbox.

If you see an encoded item like an object type, Object Browser can show you the enumeration list for that thing - like text formats, as
acTextFormatHTMLRichText or acTextFormatPlain (found under AcTextFormat). And at the bottom if it is an enumeration thing, you can also see the numeric value for it, since if it has to be passed to SQL that can't see the definitions, you have the number anyway.

That's my favorite place to find what's the default member, I tried to find the list of field types but I could not, I know I've seen it somewhere.

 

[Pat Hartman]

The Currency data type is fixed at four decimal places. Single and Double are variable so there is no property that will tell you. Byte, Integer, and Long Integer are integers and so have no decimal places.

The only data type that is variable but fixed is Decimal. It's Scale property may tell you.

If you want to analyze the data, build a function to return the longest decimal value.

 

[561414]

@Pat Hartman All number types load with a DecimalPlaces property, I don't know why, they just do. The GUI of the table designer also shows the decimal places field. I'm not saying it should be specified, I just mention that if you check for that property from VBA, it will let you access it unless it's an AutoNumber, they don't have that property when they load, but their constructor looks almost the same.

 

[Pat Hartman]

The Decimal places property is only used for purposes of display. It does NOT control what is actually stored. It only controls what you "see". Therefore, it is not relevant for documenting a table. It also does not exist unless it has been defined.

My documenter uses this code to obtain the value. The code is out of contxt so I've included the table and then the field loop so you can see the names of those proeprties.

......
    For Each tblLoop In db.TableDefs
        If Left(tblLoop.Name, 4) = "MSys" Or Left(tblLoop.Name, 2) = "xx" Or Left(tblLoop.Name, 2) = "zz" Or Left(tblLoop.Name, 1) = "~" Or Left(tblLoop.Name, 2) = "f_" Then
        Else

......
            For Each fldLoop In tblLoop.Fields
                rsFields.AddNew

                If PropertyExists(fldLoop, "DecimalPlaces") = True Then
                    rsFields!DecimalPlaces = fldLoop.Properties("DecimalPlaces")
                End If

.......

 

[561414]

I created a table with the most common data types and named its columns according to its type, the result of checking for DecimalPlaces was the following:

ByteNumberField
    Field Type:             2
    Prop Value:             255

IntegerNumberField
    Field Type:             3
    Prop Value:             255

LongNumberField
    Field Type:             4
    Prop Value:             255

SimpleNumberField
    Field Type:             6
    Prop Value:             255

DoubleNumberField
    Field Type:             7
    Prop Value:             255

DecimalNumberField
    Field Type:             20
    Prop Value:             255

CurrencyField
    Field Type:             5
    Prop Value:             255

This is the function:

Sub CheckDecimalPlaces()
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("DataTypes")
    Dim fld As Field
    Dim prop As Property
    
    For Each fld In rst.Fields
        'check props
        For Each prop In fld.Properties
            If prop.Name = "DecimalPlaces" Then
                Debug.Print fld.Name & vbCrLf & _
                vbTab & "Field Type: ", fld.Type & vbCrLf & _
                vbTab & "Prop Value: ", prop.Value & vbCrLf
            End If
        Next prop
    Next fld
    
    rst.Close
    Set rst = Nothing
End Sub

Attached is a sample. You might want to check your PropertyExists function.

 

[MajP]

Here is a nice piece of code to get the field type names from the constants.

Function FieldTypeName(fld As DAO.Field) As String
    'Purpose: Converts the numeric results of DAO Field.Type to text.
    'Source/Copyright: Allen Browne
    'URL: http://allenbrowne.com/func-06.html
    Dim strReturn As String    'Name to return

    Select Case CLng(fld.Type) 'fld.Type is Integer, but constants are Long.
        Case dbBoolean: strReturn = "Yes/No"            ' 1
        Case dbByte: strReturn = "Byte"                 ' 2
        Case dbInteger: strReturn = "Integer"           ' 3
        Case dbLong                                     ' 4
            If (fld.Attributes And dbAutoIncrField) = 0& Then
                strReturn = "Long Integer"
            Else
                strReturn = "AutoNumber"
            End If
        Case dbCurrency: strReturn = "Currency"         ' 5
        Case dbSingle: strReturn = "Single"             ' 6
        Case dbDouble: strReturn = "Double"             ' 7
        Case dbDate: strReturn = "Date/Time"            ' 8
        Case dbBinary: strReturn = "Binary"             ' 9 (no interface)
        Case dbText                                     '10
            If (fld.Attributes And dbFixedField) = 0& Then
                strReturn = "Text"
            Else
                strReturn = "Text (fixed width)"        '(no interface)
            End If
        Case dbLongBinary: strReturn = "OLE Object"     '11
        Case dbMemo                                     '12
            If (fld.Attributes And dbHyperlinkField) = 0& Then
                strReturn = "Memo"
            Else
                strReturn = "Hyperlink"
            End If
        Case dbGUID: strReturn = "GUID"                 '15

        'Attached tables only: cannot create these in JET.
        Case dbBigInt: strReturn = "Big Integer"        '16
        Case dbVarBinary: strReturn = "VarBinary"       '17
        Case dbChar: strReturn = "Char"                 '18
        Case dbNumeric: strReturn = "Numeric"           '19
        Case dbDecimal: strReturn = "Decimal"           '20
        Case dbFloat: strReturn = "Float"               '21
        Case dbTime: strReturn = "Time"                 '22
        Case dbTimeStamp: strReturn = "Time Stamp"      '23

        'Constants for complex types don't work prior to Access 2007 and later.
        Case 101&: strReturn = "Attachment"         'dbAttachment
        Case 102&: strReturn = "Complex Byte"       'dbComplexByte
        Case 103&: strReturn = "Complex Integer"    'dbComplexInteger
        Case 104&: strReturn = "Complex Long"       'dbComplexLong
        Case 105&: strReturn = "Complex Single"     'dbComplexSingle
        Case 106&: strReturn = "Complex Double"     'dbComplexDouble
        Case 107&: strReturn = "Complex GUID"       'dbComplexGUID
        Case 108&: strReturn = "Complex Decimal"    'dbComplexDecimal
        Case 109&: strReturn = "Complex Text"       'dbComplexText
        Case Else: strReturn = "Field type " & fld.Type & " unknown"
    End Select

    FieldTypeName = strReturn
End Function