Using the date on one form to limit the records from another table
Try this --
SELECT [Forms]![frm852COQ]![Month], Count([ComplaintDate]) AS Complaints
FROM Complaints
WHERE DateSerial(Year([ComplaintDate]), Month([ComplaintDate]), 1) =
[Forms]![frm852COQ]![Month]
GROUP BY [Forms]![frm852COQ]![Month];
--
Build a little, test a little.
"Bob Waggoner" wrote:
How do I limit the records in a query to a certain month? Example: I want to
know how many records (count) there are in a table for the month of September
2009 from one table - but use the date on a form from another table.
Table 1: Complaints Table 2: CostOfQuality
Table 1: Date Field: "ComplaintDate" Table 2: Date Field: "Month" (always
entered as the first day of the month like 9/1/09.)
Form: frm852COQ
The report uses Table2 as the record source (the current record on frm852COQ)
I need to total the number of complaints from table 1 for the current month
and display the count on the report.
Any help is appreciated. Thanks.
|