Is it Possible?

[Isaac Sanchez]

I have a report that gets its data from a query
The query requires input to narrow down the selection.
The query pulls data to print a load order for a delivery route.
The first prompt is the "Delivery Week?" at that point I will type
"48" for this week.
What I want to see after doing so is what my options are for
the next prompt, Which is "Delivery Route?" and have it look
something like this "Delivery Route? A, B, C, D, or E"
I have had up to 5 routes (A, B, C, D, & E) but average is 3.
AND
After that selection is made I NEED to see how many boxes
are in the route so I know what trailer to put it in. So I can
enter the next part that tells the trailer. this prompt needs to
look like this "Trailer? 300 Boxes" the number can be pulled
from another query I have that totals the box count for the route.

I tried making a form that would do this but I cannot get it to work.
I have been using Access 2000 may be I should try it on 2003 at work.

Anybody have any ideas?
TIA
Isaac
P.S. - SQL Code
SELECT DISTINCTROW DeliverT.WEEK, DeliverT.[STOP ID], DeliverT.CONSULTANT,
DeliverT.ROUTE, DeliverT.[RTE #], DeliverT.[CARTON TOTAL], DeliverT.WEIGHT
FROM DeliverT
WHERE (((DeliverT.WEEK)=[Week?]) AND ((DeliverT.ROUTE)=[Route?]));

 

[Al Campagna]

Isaac,
Sounds like this would be difficult to accomplish with only InputBox prompts, or query
parameters...
Try creating a "dialog" form that allows the user to enter all the parameters of the
report, and see the results of those parameters before running the report.
The query behind the report can get all it's criteria directly from the open Dialog
form.

I'll use the first question as an example...
Place an unbound text control on the form called DeliveryWeek, and place a 48 in it.
The query behind the report create a calculated column like this...
WeekNumber : DatePart("ww", YourDateField)
with a criteria against that of...
= Forms!frmDialofgForm!DeliveryWeek

Now the query will deliver only those dates with a WeekNumber of 48.

Now add the second question for the user to the dialog form, and continue to adjust the
report query accordingly...