Determining field datatypes - How is this done?

[CHeighlund]

How does one determine the data type for a field in an Access Database? I'm trying to work with a Delphi program, but I keep hitting a dead end where the error message is 'Data Type mismatch in criteria expression'. I'm not the one who originally built this database, nor do I work on it regularly, and I have little experience with Access in general, so I have no clue what I'm looking for here.

 

[lespaul]

Do you have the Access database? If so, just open the table properties and you'll be able to see what the field definitions are....

if not, then you need to try adding or removing ' marks from the query criteria....what's the SQL?

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Remou]

You could play about.

Sub ListTypes()
Dim rs As DAO.Recordset
Dim tdf As TableDef
Dim db As Database

Set db = CurrentDb

'#1
strSQL = "Select * From Table1"
Set rs = db.OpenRecordset(strSQL)

For Each fld In rs.Fields
    Debug.Print fld.Name & " - " & FieldTypeX(fld.Type)
Next

'#2
Set tdf = db.TableDefs("Table1")
For Each fld In tdf.Fields
    Debug.Print fld.Name & " - " & FieldTypeX(fld.Type)
Next

'#3
Dim cn As New ADODB.Connection, cn2 As New ADODB.Connection
Dim rs2 As ADODB.Recordset
Dim connString As String

Set cn = CurrentProject.Connection

Set rs2 = cn.OpenSchema(adSchemaColumns, _
    Array(Empty, Empty, "Table1"))
While Not rs2.EOF
    Debug.Print "     " & rs2!COLUMN_NAME
    Debug.Print "     " & FieldTypeY(rs2!Data_Type)
    rs2.MoveNext
Wend

End Sub

Function FieldTypeX(intType As Integer) As String

    Select Case intType
        Case dbBoolean
            FieldTypeX = "dbBoolean"
        Case dbByte
            FieldTypeX = "dbByte"
        Case dbInteger
            FieldTypeX = "dbInteger"
        Case dbLong
            FieldTypeX = "dbLong"
        Case dbCurrency
            FieldTypeX = "dbCurrency"
        Case dbSingle
            FieldTypeX = "dbSingle"
        Case dbDouble
            FieldTypeX = "dbDouble"
        Case dbDate
            FieldTypeX = "dbDate"
        Case dbText
            FieldTypeX = "dbText"
        Case dbLongBinary
            FieldTypeX = "dbLongBinary"
        Case dbMemo
            FieldTypeX = "dbMemo"
        Case dbGUID
            FieldTypeX = "dbGUID"
    End Select

End Function

Function FieldTypeY(intType)
Select Case intType
    'Case adArray
    '    FieldTypeY = "adArray"
    Case adBigInt
        FieldTypeY = "adBigInt"
    Case adBinary
        FieldTypeY = "adBinary"
    Case adBoolean
        FieldTypeY = "adBoolean"
    Case adBSTR
        FieldTypeY = "adBSTR"
    Case adChapter
        FieldTypeY = "adChapter"
    Case adChar
        FieldTypeY = "adChar "
    Case adCurrency
        FieldTypeY = "adCurrency "
    Case adDate
        FieldTypeY = "adDate"
    Case adDBDate
        FieldTypeY = "adDBDate"
    Case adDBFileTime
        FieldTypeY = "adDBFileTime "
    Case adDBTime
        FieldTypeY = "adDBTime"
    Case adDBTimeStamp
        FieldTypeY = "adDBTimeStamp"
    Case adDecimal
        FieldTypeY = "adDecimal"
    Case adDouble
        FieldTypeY = "adDouble "
    Case adEmpty
        FieldTypeY = "adEmpty "
    Case adError
        FieldTypeY = "adError"
    Case adFileTime
        FieldTypeY = "adFileTime "
    Case adGUID
        FieldTypeY = "adGUID "
    Case adIDispatch
        FieldTypeY = "adIDispatch "
    Case adInteger
        FieldTypeY = "adInteger "
    Case adIUnknown
        FieldTypeY = "adIUnknown "
    Case adLongVarBinary
        FieldTypeY = "adLongVarBinary "
    Case adLongVarChar
        FieldTypeY = "adLongVarChar"
    Case adLongVarWChar
        FieldTypeY = "adLongVarWChar "
    Case adNumeric
        FieldTypeY = "adNumeric "
    Case adPropVariant
        FieldTypeY = "adPropVariant"
    Case adSingle
        FieldTypeY = "adSingle "
    Case adSmallInt
        FieldTypeY = "adSmallInt "
    Case adTinyInt
        FieldTypeY = "adTinyInt "
    Case adUnsignedBigInt
        FieldTypeY = "adUnsignedBigInt "
    Case adUnsignedInt
        FieldTypeY = "adUnsignedInt "
    Case adUnsignedSmallInt
        FieldTypeY = "adUnsignedSmallInt"
    Case adUnsignedTinyInt
        FieldTypeY = "adUnsignedTinyInt "
    Case adUserDefined
        FieldTypeY = "adUserDefined"
    Case adVarBinary
        FieldTypeY = "adVarBinary "
    Case adVarChar
        FieldTypeY = "adVarChar "
    Case adVariant
        FieldTypeY = "adVariant "
    Case adVarNumeric
        FieldTypeY = "adVarNumeric"
    Case adVarWChar
        FieldTypeY = "adVarWChar "
    Case adWChar
        FieldTypeY = "adWChar"
    End Select
End Function

 

[CHeighlund]

Thanks. Found the database, popped into it, and took a look. One of the fields was a date/time field, and I'm used to PostgreSQL's method of marking date/time values, which isn't shared by Access. (PostgreSQL requires that date/time values be enclosed in single quotes.) Took out the quotes, and it works like a charm now. Thanks again for the advice.