Find out if a field exists in a query.

[johns88]

I have a report which runs against a crosstab query which generates a monthly summary of sales by agent.

Every month a new month is added to the data. The query automatically includes the data but the report must be modified monthly to include the new month's data.

I'd like to generate a function that I use in the report that allows me to input text boxes for each month of the year but have a calculated field in each text box.

The calculated field would check to see if the field exists in the query and if so place the value of the field in the text box. If the field does not exist then show null.

ie: iif("TableName". "FieldName" exists, then [Jan2002], else Null)

I've tried a few code samples I've found on the Net but I'm still unable to find a working solution.

Any suggestions will be greatly appreciated.

Thanks.

 

[RickSpr]

This function will do exactly what you've asked for:

    Function QueryHasField(QueryName As String, _
                           FieldName As String) _
                           As Boolean
        Dim fld As Field

        On Error GoTo ErrorExit
        Set fld = CurrentDb.QueryDefs(QueryName).Fields(FieldName)
        QueryHasField = True
    ErrorExit:
        Set fld = Nothing
    Exit Function

However, if you put this into the IIf function and use it as a control source, it will execute for every single row in your recordset, which is inefficient.

What I'd suggest is that in your Report_Open event, you use this function to test whether the query contains the field. If so, set your Control Source property to a string containing the field name. If not, set your Control Source property to an empty string, making it unbound. Rick Sprague

 

[MichaelRed]

An easier (IMHO "Better&quot approach is to set up a small recordset which includes the "Months" of interest and use this as the outter of a left join in the crosstab query (As a "Group By field). This will FORCE the inclusion of the months of interest into the crosstab query.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over