Two Queries / One Report

[lzook88]

Here I go at trying to explain this.. I have two queries. The first one pulls all records between two dates entered on a form. The second one then takes that date and counts the number of times each item appears. Then I have a report that lists out the data from the second query.

Query 1 (qryTransfersBetweenDate1ANDdate2)
INAME (Name of item from tblProducts)
OldLocation (Where the item was transferred from, from tblTransfers)
DateTransferred (The date the item was transferred, from tblTransfers)

Query2 (qryTransfersCountBetweenDate1ANDdate2)
ProductName Field (IName from Query1)
NumberOfDups (Count field to count all items from Query1 (NumberOfDups: IName)

Report (Yearly)
Product Name
Number of Dups

Form (Yearly Transferred)
Start Date


End Date

The idea is that you would enter the Start Date and End Date on the form. It would update Query 1 with those dates. Then the report would open and be sorted down to those results.

All the queries work.. The issue I am having is when I try to add in the form with the dates I want to use. These dates can change based on what the user is trying to look at.

I tried using this code in the Form's submit button:

DoCmd.OpenQuery "qryTransfersBetweenDate1ANDdate2", acViewNormal
DoCmd.ApplyFilter , "tblTransfers.[Date Transferred] Between #" & Me.StartDate & "# And #" & Me.EndDate & "#"

DoCmd.OpenReport "Yearly", acViewReport, , , acDialog

Any help would be greatly appreciated.

Thanks,
Z

[ItsMe]

Not sure it is what you are after, but you can apply criteria to a Report's Filter property when opening the report via DoCmd.

DoCmd.OpenReport "Yearly", acViewReport, , "Between #" & Me.StartDate & "# And #" & Me.EndDate & "#"

[lzook88]

I run the report off the second report. So there is no connection to the first query. The dates don't show up on the actual report.

[ItsMe]

Hmmm, well, I can say with confidence that you can do something better with your variables than using them as a filter for your table. So maybe you can use them in a query if you do not want your report to use the variables. Not too sure where else you could possibly use the variables.

[ssanfu]

Let me see if I understand:

1) You have a form with two text controls that have dates entered and a button that opens the Report.
2) Query1 (qryTransfersBetweenDate1ANDdate2) has the dates in the criteria for field "DateTransferred"
(criteria for "DateTransferred" looks like Between Forms!FormName.Date1 and Forms!FormName.Date2 )
3) Query2 (qryTransfersCountBetweenDate1ANDdate2) used query1 as the source (Query2 is a totals query?)
4) Report "Yearly" is bound to Query2 (qryTransfersCountBetweenDate1ANDdate2)

You enter the dates in the two controls, then click the button.
Other than validation code (check for proper dates or no data), I would think the code to open the report would be

DoCmd.OpenReport "Yearly", acViewReport

Query1 does all of the "filtering", so now "filter" needs to be set.


Am I close?

[lzook88]

" Between Forms!FormName.Date1 and Forms!FormName.Date2 " you have found the fix.. I knew it had to be something simple. This should work perfectly doing it this way.


When adding it to my query I found it should be:
Between Forms!FormName!Date1 and Forms!FormName!Date2

Thanks,\
Z

[ssanfu]

Sometimes the dot works, sometimes need to use the bang.

You DID change "FormName" to your form name.... right???

Glad you figured it out.....

[lzook88]

Yeah. I did that.. I'm not that big of a noob with this stuff.. Just have an occasional brain fart and cant remember my head from my backside. :-) and I turn to here to get my head situated back on my shoulders and out of my rear!