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

 

[kingston via AccessMonster.com]

What you describe is best done using a form. Here are some pointers to help
you get started. Create a form (name it Form1) with a textbox control (name
it Text1) and combobox control (name it Combo1). Text1 is where the user
enters the week number. Combo1 is where the user selects a route. Create a
query to return the data you want and in the criteria section for WEEK, type
[Forms]![Form1]![Text1]. In the criteria section for ROUTE (on the same line)
, type [Forms]![Form1]![Combo1]. Run the form and enter data into the two
controls. Now open the query to see the results that correlate with the user
input in the form. You can extend this to other fields and controls. Good
luck.

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?]));