Using Conditions In Macro

M

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.
 
S

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top