Data type of a table field
Is there any way to find out the data type of a field of a table?
in the following code, how can I find out the data type of rs.fields(1).
in the following code, how can I find out the data type of rs.fields(1).
Set cn = CurrentProject.Connection
rs.CursorLocation = adUseClient
sql = "SELECT * FROM Master WHERE ID>100 ORDER BY ID"
rs.Open sql, cn, adOpenDynamic, adLockOptimistic
With rs
......
......
......
......
end withASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
mx:
your suggested code behaves strange:
.Fields(1).Type for Text Data types returns 202.
More over the indexes in Choose statement are shifted. I believe it's because the items in choose doesn't follow the following list.
Still I can't understand what this 202 is.
The code suggested by pratima_mcs works perfect.
Since I prefer to work with recordset., I'll wait for some more suggestions.
Otherwise I will accept pratima_mcs' code.
thank you
your suggested code behaves strange:
.Fields(1).Type for Text Data types returns 202.
More over the indexes in Choose statement are shifted. I believe it's because the items in choose doesn't follow the following list.
Still I can't understand what this 202 is.
The code suggested by pratima_mcs works perfect.
Since I prefer to work with recordset., I'll wait for some more suggestions.
Otherwise I will accept pratima_mcs' code.
thank you
vbEmpty 0 (uninitialized)
vbNull 1(no valid data)
vbInteger 2
vbLong 3
vbSingle 4
vbDouble 5
vbCurrency 6
vbDate 7
vbString 8
vbObject 9
vbError 10
vbBoolean 11
vbVariant 12
vbDataObject 13
vbDecimal 14
vbByte 17
vbLongLong 20
vbUserDefinedType 36
vbArray 8192
Glad to help you
ASKER
Million Thanks
I got the Types from the image below - on Allen's site. If you are using the solution above, then you are missing several data types, including Text! The Indexes in the Choose statement directly correspond to the Types shown in the image below.
mx
Capture1.gif
mx
Capture1.gif
Dim cn, sql, rs As ADODB.Recordset
Dim tType
Set cn = CurrentProject.Connection
rs.CursorLocation = adUseClient
sql = "SELECT * FROM Master WHERE ID>100 ORDER BY ID"
rs.Open sql, cn, adOpenDynamic, adLockOptimistic
With rs
tType = Choose(.Fields(1).Type, "Yes/N0", "Byte", "Integer", "Long", "Currency", "Single", "Double", "Date/Time", "Binary", "Text", "OLE Object", "Memo")
Debug.Print tType
End With
mx