Variable In Query For Report

[Tim]

Ok, what a subject title.

I am not sure what to call it, but here is what I want to do....

Query: AllOrders, about 9 fields, and 1 of them is determining what to pull
out. It uses the BoatID (numeric) to get a list of all orders. Example:
BoatId=5

Report: AllOrdersByBoat; pulling data from the query AllOrders.

Problem: If I want to print all orders for BoatId=9, I have to go change the
query to BoatId=9, then resave it. Then print the report.

We are trying to go away from the Query for every boat strategy we first
implemented. Right now there is a query for each and every boat AND a
report for each and every query. We are trying to get something going with
ONE query and utilizing ONE report.

The underlying data and format of the report is identical, we just want a
way to CHOOSE the boat we want the report on.

Can someone tell me what I need to put in the Query so that it will ask the
End User this field (BoatID); and if the report when pulling up this query
will ask that question? I have this vision of pressing the Report:
AllOrdersByBoat, and it goes out to bring up the query and when it does, it
ask for the BOATID, and prints the report based on that boat number.

Is this possible?

Tim

 

[Conan Kelly]

Tim,

Change...

BoatId=9

....to...

BoatId=[Please enter a Boat ID:]

That will pop up an input box asking for a boat id every time that query is
run, whether it is run directly or run via opening up a report.

HTH,

Conan

 

[Jeff Boyce]

Tim

Conan offers a way to modify the query to prompt the user for a BoatID and
return only that Boat's record(s) for a report.

Another approach would be to use a form on which you place a combobox. In
the combobox, use a query that returns all the boats (actually, all the
BoatIDs AND any other fields that might make it easier for a user to select
a boat). Modify your first query to look at the form for the value in the
combobox, instead of prompting for an ID.

Many users find a text/descriptive choice easier to remember than an ID...

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Duane Hookom]

Make that J2PO. See why at http://www.tek-tips.com/faqs.cfm?fid=6763.

 

[Tim]

Tim,

Change...

BoatId=9

...to...

BoatId=[Please enter a Boat ID:]

That will pop up an input box asking for a boat id every time that query
is run, whether it is run directly or run via opening up a report.

Conan, thank you. That was the trick. Now it works great. 1 Report and
just let the variable determine what boat I want to work with.

Tim

 

[Conan Kelly]

Tim,

This method is very limited. I don't know how many boats you have to work
with. If the user could mistype the boat ID, it could cause problems,
return no records, or return records/report for the wrong boat.

Also, this only allows for 1 boat. If you ever wanted to set things up to
work with any number of boats, you will have to change the design.

A better way would be to create a form and have a user enter a value in a
text box, or select from a list in a combo/list box. Then you would change
your criteria from:

BoatId=[Please enter a Boat ID:]

....to...

BoatId=[Forms]![FormName].[ControlName]

(don't quote me on that...I think my syntax is correct...but if you wanted
to use it, it would need verification).

HTH,

Conan

Tim,

Change...

BoatId=9

...to...

BoatId=[Please enter a Boat ID:]

That will pop up an input box asking for a boat id every time that query
is run, whether it is run directly or run via opening up a report.

Conan, thank you. That was the trick. Now it works great. 1 Report and
just let the variable determine what boat I want to work with.

Tim