How to programatically determine a field type? 1

[vanleurth]

Hi happy programmer,

I trying to write a filter for my tables that will use string comparisons. For exmaple,

If field1 > number then
' Perform an aritmatic calc. and greater than or smaller than comparisons
if else field1 = text then
' String comparison and do not perform a greater or smaller comparison
if else field1 = boolean type then
' Perform a one or zero comparison and do not perform a greater than or smaller than comparison.

Any help is appretiated,

Thanks,

V.

 

[hneal98]

you can look up the functions isnull, isnumeric, isdate, etc... Try those and if you still have problems, let us know.

Also, on your if statements, it is normally else if, not if else.

Thanks.

 

[Dimandja]

I don't have the exact syntax, but look into

fieldType = field("any").type

 

[cmmrfrds]

Tables and Recordsets have associated Fields Collections. For Tables you can use either the DAO tabledef or ADOX catalog. Tabledef is for an Access mdb only, while ADOX is available with OLEDB Provider including Access.

One of the properties in the fields collection is the data type.

Field collection on a Recordset Object.

sql1 = "select * from c:\atestdir\newtest.mdb.dbo_orders "
rs.Open sql1, cn, adOpenStatic, adLockOptimistic

Dim fl As ADODB.Field, indx As Integer
For Each fl In rs.Fields
Debug.Print "The Field Name = "; rs.Fields(indx).Name
Debug.Print "The Field Type = "; rs.Fields(indx).type
Debug.Print rs.Fields(indx).OriginalValue
Debug.Print rs.Fields(indx).Value
indx = indx + 1
Next

An example of going through a Property collection on a table using ADOX – similar for a Fields collection.

Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table

Set cg.ActiveConnection = CurrentProject.Connection

For Each tb In cg.Tables
''Debug.Print "table = "; tb.Name
''Debug.Print "----type = "; tb.type
If tb.type = "LINK" Then
Debug.Print "table = "; tb.Name
Dim pp As ADODB.Property
For Each pp In tb.Properties
Debug.Print "property name = "; pp.Name
Debug.Print "property value = "; pp.Value
Next
End If
Next