checking database field names

hi all, i have a foxpro table that i can connect to.
I need to put a handle for when the table cannot find the requested field name, the error it has is:

error :"40041 Object Collection: Couldn't find item indicated by text."

Can i put a check like

if isnull(rs!Date) then
Mydate=date
else
Mydate = rs!date
end if

....but check if the field data cannot be found.

I have tried this but it comes up with the same error.

thanks in advance.
Dave

The code is failing because, as you indicate, there's no field by that name to even check for null.

One way to check for existance of a field is to iterate through all the fields, looking at their names.

For example, here's a function (untested) that would check for existance of a field name in a recordset.

Function IsField (ByRef rsCheck as ADODB.Recordset, ByRef strFieldName as String) as Boolean
' Function returns TRUE if the field name is in the recordset

Dim N as Integer
Dim strFN as String

strFN = trim(strFieldName)

IsField = False
Do While N < rsCheck.Fields.Count-1
If strFN = rsCheck.Fields(N).Name then
IsField = True
End If
N = N + 1
Next N

Exit Function

Note; the function has no error checking for recordset state, so you'll need to ensure that the recordset is open before use, or add error checking (if rsCheck.State = adStateOpen then...)

And, by the way: a field named DATE is an inappropriate choice, as both VB and most database servers reserve that name as a function...

I know someone will shoot me again for using “on error goto” code but it’s the only solution I know to solve this


Public Function CheckFieldName(rs As Recordset, fieldname As String) As Boolean
On Error GoTo ErrorHandler
Dim tstr As String
tstr = rs.Fields(fieldname).Name
CheckFieldName = True
Exit Function
ErrorHandler:
CheckFieldName = False
End Function