Data Type of a field in a table

[aziz rasul]

Is their any code in which I can find what data type the fields are in a table?

 

[ErikSnoek]

Put the following code in a module and run showTableFieldTypes(). I only added 4 common data types in the daoDataTypeByNum function, but as you can see it's quite easy to expand

Sub showTableFieldTypes()
    Dim tblTable As TableDef
    Dim tblField As Field
    For Each tblTable In CurrentDb.TableDefs
        For Each tblField In tblTable.Fields
            Debug.Print tblTable.Name & " - " & tblField.Name & ": " & daoDataTypeByNum(tblField.Type)
        Next
    Next
End Sub
 
Function daoDataTypeByNum(Num As Long) As String
    Select Case Num
        Case dbText
            daoDataTypeByNum = "Text"
        Case dbLong
            daoDataTypeByNum = "Number / Long Integer"
        Case dbDate
            daoDataTypeByNum = "Date"
        Case dbMemo
            daoDataTypeByNum = "Memo"
    End Select
End Function

 

[aziz rasul]

Thanks. That's very useful code.

 

[gemma-the-husky]

or a variable

case vartype(myvar)

in which case the constants are of type vbxxxxxx

so we have vblong, vbdouble, vbdate etc

 

[raskew]

Or this:

Function GetFields(pStr As String) As String
Dim db     As Database
Dim fld    As Field
Dim tdf    As TableDef
Dim strSql As String

    Set db = CurrentDb
    Set tdf = db.TableDefs(pStr)
    strSql = ""
    For Each fld In tdf.Fields
       strSql = strSql & fld.Name & "--" & Choose(fld.Type, "Boolean", "Byte", "Integer", "Long", "Currency", "Single", "Double", "Date", "Binary", "Text", "LongBinary", "Memo", , , "GUID") & "; "
    Next fld
    GetFields = Left(strSql, Len(Trim(strSql)) - 1)
End Function

To call from debug (immediate) window:
? getfields("Orders3")

Returns
OrderID--Long; CustomerID--Text; EmployeeID--Long; OrderDate--Date; RequiredDate--Date; ShippedDate--Date; ShipVia--Long; Freight--Currency; ShipName--Text; ShipAddress--Text; ShipCity--Text; ShipRegion--Text; ShipPostalCode--Text; ShipCountry--Text


Bob