Access2K report doesn't like xtab query results ... sometimes 1

[Glenn302]

I may have a similar problem like HEATHCO.

I have an existing report which is populated by a xtab query. When the xtab does not generate all the fields on the report, I get an error message about the MS Jet database engine not recognizing the 'missing' data item as a field name or expression.

Is there a VBA function I can incorporate in the report fields that will ignore the 'missing' data item?

Since the xtab query generates numeric data items (totals) based on a code type (e.g., 1, 2, 6, 7, etc.), the field names are numeric (i.e., 1, 2, 6, 7, etc.). Is there a way I can assign more meaningful names on the query?

Thanks!

 

[MichaelRed]

For the missing fields, one approach is to Make a table/query which is "guarnteed" to have the fields the report needs and generate a layered query group. 'First' layer is the XTab. 'Next' layer is a select w/ the XTAB (real Data) and the table/Query w/ the Fields REQUIRED for the Report. Left Join on the Field Names. All "Required" fields are FORCED into the 'Net' layer query, so no more missing fields.

An Alternative to this - for XTab Queries, there is a property "Fields"?. You can "Type" in field names in this property and Ms. Access will FORCE the fields to exist in the XTab. I do not prefer this, as I'm somewht forgetful and don't rember ot update the property (actually forget it even exists) When something changes, I am bewildered by the failure of the report ...

For the second question, again, the layer offers a soloution. Just 'alais' the field names in the 'next' query.


MichaelRed
redmsp@erols.com

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