Using 2 queries to populate a report

[tikkat3]

Can 2 queries be used to populate a report? I have a report that is totally finished, except for one field. The one field needs to be populated with data from a separate query, which in turn takes its data from a linked table from another database.

Try what I may, this field will not take the 2nd query as its control source. It just asks me to enetr parameters.

If I try to add the 2nd query into the 1st query all I get is wild and inaccurate results which affects the rest of the report.

 

[boblarson]

You need to use a sub form if you want to use another query, or if you are just displaying one item in a text box for the report, a DLookup.

 

[tikkat3]

All I need from the second query is the count of the number of items listed in one of the columns. The results need to be limited to being between the two dates required for the rest of the report.

Can DLookup handle this, if so how would I go about it? I have never used DLoopup!

Thanks.

 

[boblarson]

recordsource for the text box:

=DLookup("[YourFieldNameInTheQuery2]", "YourQuery2NameHere", "[DateField] Between #" & Me.YourfirstDateBox & "# And #" & Me.YourSecondDateBox & "#")

It's been awhile since I used a between for a Dlookup, so it could be IS BETWEEN, I can't remember.

 

[tikkat3]

Bob,

Have I understood right? This is what I have entered:

=DLookUp("[C/Q Number]","qryFixCount","[Date] Between #" & [Date] & "# And #" & [Date] & "#")


[C/Q Number] is the field within query 2.
qryFixCount is the name of query 2.
[Date] is the name of the date field that is being restricted using BETWEEN AND in the main query 1.

Query 1's [Date] prompts for a start date and and end date when first running the query.

I am getting an error result.

 

[boblarson]

First of all, if you have a column or field named DATE, change it now. Date is a reserved word in Access and will only cause you problems (some of which we could be viewing right now) if you use it that way. Never use reserved words (words of functions, system objects, etc.) as object names.

 

[tikkat3]

Changed. It was actually the name of the field in the report only. The table behind it had a different name. Still no results with the dlookup expression.

 

[tikkat3]

Ok, I have gone down the sub report route and am getting the desired results. Much simpler for me to do!

There is an issue though with the date restriction. I have both the report and sub report set up so that a start date and end date are required, so restricting the results to a certain time period.

When I run the report I get asked to enter both the start and end dates three times, and again three times when I change page. If I try to link the dates in both reports as Master/Child the same thing happens and I get false sub report results.

Is it normal to have to enter the dates 3 times?

 

[boblarson]

When I run the report I get asked to enter both the start and end dates three times, and again three times when I change page.

Instead of using parameteres of [Enter Date] set the critera based on a form so the user can select, or input, the dates and then the queries can look there and entry is made once.