Thanks--this was helpful.
To better explain what I'm doing: I need to create 15 separate reports (on
an on-going basis). The report layout is the same, only the data
changes--usually it's just a different division--so I'm inputting the
division codes in the parameter query to create the reports. I'd rather just
create a macro that simulates this sequence since these reports need to be
generated regularly. I just discovered that the SendKeys action types in
values if you put it before the openquery action. If I could simulate
pressing the Enter key, then I would have solved this with a macro. Can you
simulate pressing the Enter key with the SendKeys action?
If not, then I have a complicated parameter query that perhaps you can help
me streamline. If it was simply 15 divisions that needed to be prompted then
your suggestion of: "[Enter division code] is null" is all I would need.
For several of the divisions, however, they also want a certain department
excluded from that division, but then that department then gets its own
report.
I have it working in a complicated set of four parameter prompts. The first
line looks like this: (Under the division code field) Like "*" & [Enter
Division code to include]
(Under the department code field) Not Like "*" & [Enter Department to exclude]
This gets me the special divisions that exclude a department.
Then on the next Division code criteria line down (OR line), I have:
[Enter Division code]
to get the normal divisions (all departments)
Then on the next Department code criteria line down (OR line), I have:
[Enter Department code]
to get the previously excluded department all by itself.
This actually gets me my data (believe it or not), but as you can imagine,
it's very confusing. I couldn't get the is null to work with this kind of
request.
if you have any suggestions, I would really appreciate it.
Thanks again!
John Vinson said:
I have a parameter query that has 15 possible responses which is then the
basis for a report. We want the report run 15 times (one time for each
parameter query response). Is there any way to automate the responses so the
user doesn't have to type in the criteria each time? I tried including the
macro actions: openquery and sendkeys, but sendkeys runs after openquery has
run.
If VBA is an alternative, I know it somewhat.
Thanks for any help!
Is this a one-shot operation? If so, I'd suggest simply removing the
criterion.
If it will be ongoing, then consider changing the criterion from
[enter parameter]
to
[enter parameter] OR [enter parameter] IS NULL
to retrieve all records if the user types nothing into the parameter.
John W. Vinson[MVP]