How do I determine the datatype in a recordset?

[ProgrammerAndy]

here is my dilemia

I open a recordset and I need to determine if my value is numeric, date, or char. It works as long as I do not have a number in a text field. The statement sees this as a numeric value yet it is not. Any suggestions on how I tell the code to treat this as a text?

Oh yes, a wrench! I am using this if/else statement in a do loop, building a where clause for a SQL statement, being passed back to SQL 2000. So the value changes for letters to numbers and back again.

Code:
If IsNumeric(rst!WhereCriteria) Then
g_strSQLWhere = g_strSQLWhere & " " & rst!WhereCriteria
Else
If IsDate(rst!WhereCriteria) Then
g_strSQLWhere = g_strSQLWhere & " CONVERT(DATETIME, '" & rst!WhereCriteria & "' , 102) "
Else

g_strSQLWhere = g_strSQLWhere & " '" & CStr(rst!WhereCriteria) & "' "
End If
End If

 

[JoeMiller]

There are a whole bunch of IsXYZ() functions that return true/false. Perhaps they could help you?

IsDate([MyValue])
IsNumeric([MyValue])

Also you can use TypeName() which returns a string of what a variable is:

?TypeName(#7/18/01#)
Date

Be careful though:

?TypeName(7/18/01)
Single

And finally you can use the VarType() function to return a constant that equates to a data type value.

?VarType(#7/18/01#)
7

7=vbDate datatype. All these functions are covered in help.

HTH Joe Miller
joe.miller@flotech.net