505.369.1650 [email protected]

If you’ve ever imported a text file or a Microsoft Excel worksheet into Microsoft Access, you have seen how Access defaults the text field lengths to 255 characters.  This VBA function will display the maximum length of the values currently in each field of a table so that you can decide how to correctly set the field lengths.  It also shows the data type of each field.

For example, if you call the function using the table that is in the download file below, you would get results in the Immediate Window like the following, with field name, field type, and the maximum value length currently in the table for that field:

?FieldTypeAndMaxValueLen(“FieldTypes”)
FieldTypeDescription, Type: Text, MaxValLen: 24
FieldTypeConstant, Type: Text, MaxValLen: 12
FieldTypeNumber, Type: Long, MaxValLen: 2
True

The FieldTypes table is a reference table that supplies the description that you see in the function results.  This table was created using DAO values; ADO has it’s own set of values.  DAO code was used because it was very simple and it works without having to add a reference in Access 2007.

 

FieldTypes
FieldTypeDescription FieldTypeConstant FieldTypeNumber
Big Integer dbBigInt 16
Binary dbBinary 9
Boolean dbBoolean 1
Byte dbByte 2
Char dbChar 18
Currency dbCurrency 5
Date/Time dbDate 8
Decimal dbDecimal 20
Double dbDouble 7
Float dbFloat 21
GUID dbGUID 15
Integer dbInteger 3
Long dbLong 4
Long Binary (OLE Object) dbLongBinary 11
Memo dbMemo 12
Numeric dbNumeric 19
Single dbSingle 6
Text dbText 10
Time dbTime 22
Time Stamp dbTimeStamp 23
VarBinary dbVarBinary 17

Code:

Public Function FieldTypeAndMaxValueLen(TableName) As Boolean
' This function returns the maximum length of value that is in each
' field in the TableName table.

' FieldTypeAndMaxValueLen() Version 1.0.0
' Copyright © 2009 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected]
' Use (at your own risk) and modify freely as long as proper credit is given.

On Error GoTo Err_FieldTypeAndMaxValueLen

    Dim fld As DAO.Field
    Dim rst As DAO.Recordset
    Dim strFieldName As String
    Dim strType As String

    ' Open the table.
    Set rst = .OpenRecordset(TableName)

    ' Loop through the collection of .
    For Each fld In rst.
        ' Get the field name.
        strFieldName = fld.Name
        strType = DLookup("FieldTypeDescription", "FieldTypes", _
            "FieldTypeNumber=" & fld.Type)
        ' Print the field name and max length of value in that field
        ' to the immediate window.  Use brackets around the field name
        ' in case it has spaces.
         strFieldName & ", Type: " & strType & ", MaxValLen: " _
            & DMax("Len(Nz([" & strFieldName & "],''))", TableName)
    Next fld

    FieldTypeAndMaxValueLen = True

Exit_FieldTypeAndMaxValueLen:
    On Error Resume Next
    rst.Close
    Exit Function

Err_FieldTypeAndMaxValueLen:
    MsgBox Err.Number & " " & Err.Description, vbCritical, "FieldTypeAndMaxValueLen()"
    FieldTypeAndMaxValueLen = False
    Resume Exit_FieldTypeAndMaxValueLen

End Function

Download Code:

FieldTypeAndMaxValueLen.zip

Making Microsoft Access Work

No matter what your MS Access needs are, Carl can provide live help to make sure you get the solution you need.