How to find out if a field exists in a crosstab.

[Rod]

I have created a recordset by creating a crosstab query in code

ie Set MyRS=Db.OpenRecordset(mySQL.,......

But becuase it is a crosstab I do not know if all the fields are always
there.


What I want to be able to do is something like

If MyRS("MyField").Exists then
MyVar=MyRS(MyField)


Any hints much appreciated.

thanks

Rod


PS, am using DAO if it makes any difference.

 

[Allen Browne]

Yes, that works. Will take a while if it's a large number of records though.

Are you aware that you can set the Column Headings property of the query, so
that all the possible field values are present even in they have no data?

 

[Rod]

Yes, that works. Will take a while if it's a large number of records

though.

?? There's no such Exists, what I meant is how do I achieve the
equivalent.

Anyhow you next comment solves my problem, many thanks

 

[Allen Browne]

Sorry: didn't read your code clearly.

Thought you'd done something like this (aircode):

Private Function FieldExists(strFieldName As String) As Boolean
Dim rs As DAO.Recordset
Dim varDummy As Variant
Set rs = dbEngine(0)(0).OpenRecordset("MyQuery")
On Error Resume Next
varDummy = rs.Fields(strFieldName)
FieldExists = (Err.Number = 0)
rs.Close
End Function

You could also dispense with the query, and just use:
varDummy = DLookup(strFieldName, "MyQuery)
Generates error 2001 if the name does not exist.

 

[Rod]

Thanks, this will be useful

Sorry: didn't read your code clearly.

Thought you'd done something like this (aircode):

Private Function FieldExists(strFieldName As String) As Boolean
Dim rs As DAO.Recordset
Dim varDummy As Variant
Set rs = dbEngine(0)(0).OpenRecordset("MyQuery")
On Error Resume Next
varDummy = rs.Fields(strFieldName)
FieldExists = (Err.Number = 0)
rs.Close
End Function

You could also dispense with the query, and just use:
varDummy = DLookup(strFieldName, "MyQuery)
Generates error 2001 if the name does not exist.