Using the date on one form to limit the records from another table

[Bob Waggoner]

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.

 

[KARL DEWEY]

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

 

[Jeff Boyce]

Bob

That will depend ... (doesn't it always!<G>)

If your table holds ONLY a single year's records (NOTE: not recommended!),
then finding "September"'s records is easy to handle several different ways,
including the approach Karl offers.

If your table holds many years' records, do you REALLY want to see (all)
Septembers' records, or only for a particular year?

"How" depends on "what"...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

 

[Bob Waggoner]

I haven't been able to get Karl's approach to work. My database holds records
from all years. In the Cost of Quality (CoQ) table, one record = one month.
We date the month 9/1/09, 10/1/09, and so on. I just want to call up the
number of complaints (total number) from the Complaints table that match the
COQ Table's current record (current month). With Karl's approach, I can
remove the "[Forms]![frm852COQ]![Month] and get all the complaints in the
table (total). With that criteria I get zero. I need to find the right date
field/format that matches the complaint table's september (09) results for
example. [The current record could be 8/1/09 in the CoQ table - which means I
want to find the total number of complaints in the complaints table for
August 09.]

Hope this explains it better and thanks.

 

[Jeff Boyce]

Bob

It sounds like you are saying that you are storing "dates" as 'first day of
month' (e.g. "9/1/09", "10/1/09", ...). Does that mean you are storing a
text string (in a text-typed field), or are storing actual dates in a
date/time field, and chosing to display (i.e., "format") the dates as
"9/1/09", ...?

How will depend on what!!

And since you want to find all records in August '09 (or some other
month/year combination), you'll want to use a selection criterion in a query
that limits returned records to those within that month/year, right?

One way to do this, if you are actually using MS Access' built-in date/time
data type (rather than a text representation) would be to use the "Between
.... And ..." selection criterion/statement. To do that, your in-query
selection criterion for your date field might look like (untested):

Between DateSerial([Which Year?], [Which Month (#)?],1) And
DateSerial([WhichYear?], [Which Month (#)?] + 1, 0)

That second DateSerial() expression has the effect of adding one month, then
taking the "zeroth" day (the day before the first of next month = the last
day of this month).

Good luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I haven't been able to get Karl's approach to work. My database holds
records
from all years. In the Cost of Quality (CoQ) table, one record = one
month.
We date the month 9/1/09, 10/1/09, and so on. I just want to call up the
number of complaints (total number) from the Complaints table that match
the
COQ Table's current record (current month). With Karl's approach, I can
remove the "[Forms]![frm852COQ]![Month] and get all the complaints in the
table (total). With that criteria I get zero. I need to find the right
date
field/format that matches the complaint table's september (09) results for
example. [The current record could be 8/1/09 in the CoQ table - which
means I
want to find the total number of complaints in the complaints table for
August 09.]

Hope this explains it better and thanks.

Bob

That will depend ... (doesn't it always!<G>)

If your table holds ONLY a single year's records (NOTE: not
recommended!),
then finding "September"'s records is easy to handle several different
ways,
including the approach Karl offers.

If your table holds many years' records, do you REALLY want to see (all)
Septembers' records, or only for a particular year?

"How" depends on "what"...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

 

[Bob Waggoner]

Jeff,
you are correct. We simply use the first day of the month to indicate what
month it is. 9/1/09 = September 09. We only make one entry each month. It is
a date field.
Thanks
Bob

Bob

It sounds like you are saying that you are storing "dates" as 'first day of
month' (e.g. "9/1/09", "10/1/09", ...). Does that mean you are storing a
text string (in a text-typed field), or are storing actual dates in a
date/time field, and chosing to display (i.e., "format") the dates as
"9/1/09", ...?

How will depend on what!!

And since you want to find all records in August '09 (or some other
month/year combination), you'll want to use a selection criterion in a query
that limits returned records to those within that month/year, right?

One way to do this, if you are actually using MS Access' built-in date/time
data type (rather than a text representation) would be to use the "Between
.... And ..." selection criterion/statement. To do that, your in-query
selection criterion for your date field might look like (untested):

Between DateSerial([Which Year?], [Which Month (#)?],1) And
DateSerial([WhichYear?], [Which Month (#)?] + 1, 0)

That second DateSerial() expression has the effect of adding one month, then
taking the "zeroth" day (the day before the first of next month = the last
day of this month).

Good luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I haven't been able to get Karl's approach to work. My database holds
records
from all years. In the Cost of Quality (CoQ) table, one record = one
month.
We date the month 9/1/09, 10/1/09, and so on. I just want to call up the
number of complaints (total number) from the Complaints table that match
the
COQ Table's current record (current month). With Karl's approach, I can
remove the "[Forms]![frm852COQ]![Month] and get all the complaints in the
table (total). With that criteria I get zero. I need to find the right
date
field/format that matches the complaint table's september (09) results for
example. [The current record could be 8/1/09 in the CoQ table - which
means I
want to find the total number of complaints in the complaints table for
August 09.]

Hope this explains it better and thanks.

Bob

That will depend ... (doesn't it always!<G>)

If your table holds ONLY a single year's records (NOTE: not
recommended!),
then finding "September"'s records is easy to handle several different
ways,
including the approach Karl offers.

If your table holds many years' records, do you REALLY want to see (all)
Septembers' records, or only for a particular year?

"How" depends on "what"...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

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.