Using Conditions In Macro

[Manuel]

I have a macro, which prints a report (let's call it
report 1). The report is based on a crosstab query, which
counts "X, Y and Z" (for simplicities sake) values in a
table. However, sometimes there's no "Z" to count, and I
get error message 3070 -

"The Microsoft Jet database engine does not recognize 'Z'
as a valid field name or expression"

- when printing/opening the report.

What I've done is create an identical report (let's call
it report 2) which only counts "X and Y". But now I'm
having trouble altering my macro to print the 2nd report
when error 3070 materializes as a result of printing
report 1.

Anyone have any suggestions? I've tried setting a
condition using the Error Handling functions, but so far
no luck.

 

[Steve Schapel]

Manuel,

There is no way to respond to an error like this via a macro, in the way
you are suggesting.

Without knowing the details of what you are doing or what you want, I am
not sure. But I think the best approach would be to use the Column
Headings property of the crosstab query to define a Z field, even if
there is no Z data. This way you don't have to mess around with
different reports for different potential data sets.

However, if you really do want a separate report if there is no Z data,
you can use a Condition in your macro. It might look something like this...
Condition: DCount("*","YourBaselineQuery","TheValue='Z'")=0
Action: OpenReport
Report Name: "X and Y only"
Condition: DCount("*","YourBaselineQuery","TheValue='Z'")>0
Action: OpenReport
Report Name: "X Y and Z"

Hope something here will at least point you in the right direction.