Two Queries for One Report

[lynnmc26]

I have two complex queries that I need in one report.

There is a parameter to input the date for the box history query however when I put both queries in a report the parameter appears the same number of times there is an entry - ie - asks me for the date 4 times if there are four items in the box history.



What can I do to make the parameter appear just once?

Here are the two queries

#1:
SELECT FOLDERS.IMBOXNO, FOLDERS.FILENO, BOXES.ACCOUNTCODE, FOLDERHISTORYTRACKING.REQUESTOR, FOLDERHISTORYTRACKING.REQUESTORLOCATION, FOLDERHISTORYTRACKING.DATEOUTCLIENT
FROM (BOXES INNER JOIN FOLDERS ON BOXES.IMBOXNO = FOLDERS.IMBOXNO) INNER JOIN FOLDERHISTORYTRACKING ON FOLDERS.FOLDERID = FOLDERHISTORYTRACKING.FOLDERID
WHERE (((FOLDERHISTORYTRACKING.REQUESTOR) Not Like 'COE' And (FOLDERHISTORYTRACKING.REQUESTOR) Is Not Null) AND ((FOLDERHISTORYTRACKING.REQUESTORLOCATION) Like '2') AND ((FOLDERHISTORYTRACKING.DATERETURNED) Is Null) AND ((TimeValue([FOLDERHISTORYTRACKING].[DATEOUTCLIENT])) Between #12/30/1899 10:5:1# And #12/30/1899 14:0:1#) AND ((FOLDERHISTORYTRACKING.RUSHDELIVERY)=No) AND ((DateValue([FOLDERHISTORYTRACKING].[DATEOUTCLIENT]))=Date()));

#2:
SELECT BOXES.IMBOXNO, BOXES.ACCOUNTCODE, BOXES.ADDITIONALBOXNO, BOXHISTORYTRACKING.REQUESTOR, BOXHISTORYTRACKING.REQUESTORLOCATION, BOXHISTORYTRACKING.DATEOUTCLIENT
FROM BOXES INNER JOIN BOXHISTORYTRACKING ON BOXES.IMBOXNO = BOXHISTORYTRACKING.IMBOXNO
WHERE (((BOXHISTORYTRACKING.REQUESTOR) Not Like 'COE' And (BOXHISTORYTRACKING.REQUESTOR) Is Not Null) AND ((BOXHISTORYTRACKING.REQUESTORLOCATION) Like '1') AND ((BOXHISTORYTRACKING.DATEOUTCLIENT)=[ENTER DATE OUT - eg - mm/dd/yyyy]) AND ((BOXHISTORYTRACKING.RUSHDELIVERY)=No) AND ((TimeValue([BOXHISTORYTRACKING].[PROCESSDATE])) Between #12/30/1899 10:5:1# And #12/30/1899 14:0:1#));

Let me know if you have any questions.

Thanks!
Lynn

[June7]

How does a report have two queries. Is this a report/subreport arrangement?

Have user input date to a control on a form. Have the queries refer to the control as the input parameter.

[lynnmc26]

The report is like a shipping manifest
I insert each query into the body of the report
One (box query) has a paramater to ask for a date.

I can't do the control on the form for this to work.

Any other suggestions?

[rpeare]

It sounds to me like you're using more than 2 queries. The two queries you're listing are showing the data, but what's the data source for the report itself. If you just have a sheet with two sub reports you shoudn't be prompted multiple times unless you've also got a data source for the main report.

[lynnmc26]

I know - that is what I thought - I have a blank (new) report set up with a page header and dropped the two queries into the report as individual sub reports.

[lynnmc26]

I think I have solved this - redoing my report once again and it seems to be working.