count records by date range

[PHisaw]

Please forgive the repost - someone supplied answer for running sum over
report pages and I only have one page and would like a text box with total
count for each quarter. I replied but no one picked up msg again.

I have a report broken down by quarter grouped by salesperson. I'm trying to
count the number of invoices per salesperson for each quarter. I thought I
could put a text box under each quarter with count or dcount expressions - so
far everything ends in "error" or same wrong total for each salesperson. I've
tried different variations on the following control source for textbox:
DCount("[Inv]","table1","[InvDate]=Between#1/1/07#AND#3/31/07#") along with
several Count expressions.

If someone would supply the correct expression, I would certainly appreciate
it.
Thanks,
Pam

 

[Ofer Cohen]

About the Dcount
DCount("[Inv]","table1","[InvDate]=Between#1/1/07#AND#3/31/07#")
Need to be
=DCount("*","table1","[InvDate] Between #1/1/07# AND #3/31/07#")
Removing the = sign before the between and adding a space between the values

*****
Also, in the report try
=Sum(IIf([InvDate] Between #1/1/07# AND #3/31/07#,1,0))

So it will add 1 when the date meet the criteria

 

[PHisaw]

Ofer,

Thanks for the prompt reply. The expressions worked as asked for - but, not
what was needed when applied to my report. When I created this report for
the first quarter, I used a query to group invoices (my invoice list shows
several records for each invoice to obtain item sold and dollar amount per
item) then created a second query to count those invoices for each salesman.
I used a subreport based on this last query and applied to my report not
realizing this would not work for future quarters. The DCount does as it
should - counts "all" records. The sum expression you gave me totals all
records for that period per salesperson but the invoice records are not
grouped by invoice number so my total is way off.
What I'm asking now is if there is a better way than creating a query and
subreport for each quarter?

Thanks again for your valuable help.
Pam

About the Dcount
DCount("[Inv]","table1","[InvDate]=Between#1/1/07#AND#3/31/07#")
Need to be
=DCount("*","table1","[InvDate] Between #1/1/07# AND #3/31/07#")
Removing the = sign before the between and adding a space between the values

*****
Also, in the report try
=Sum(IIf([InvDate] Between #1/1/07# AND #3/31/07#,1,0))

So it will add 1 when the date meet the criteria


--
Good Luck
BS"D

Please forgive the repost - someone supplied answer for running sum over
report pages and I only have one page and would like a text box with total
count for each quarter. I replied but no one picked up msg again.

I have a report broken down by quarter grouped by salesperson. I'm trying to
count the number of invoices per salesperson for each quarter. I thought I
could put a text box under each quarter with count or dcount expressions - so
far everything ends in "error" or same wrong total for each salesperson. I've
tried different variations on the following control source for textbox:
DCount("[Inv]","table1","[InvDate]=Between#1/1/07#AND#3/31/07#") along with
several Count expressions.

If someone would supply the correct expression, I would certainly appreciate
it.
Thanks,
Pam

 

[PHisaw]

Ofer,

Thank you for the "Sum" expression you provided - I had to tweak my query,
but I finally got it to work.

Thanks again,
Pam

About the Dcount
DCount("[Inv]","table1","[InvDate]=Between#1/1/07#AND#3/31/07#")
Need to be
=DCount("*","table1","[InvDate] Between #1/1/07# AND #3/31/07#")
Removing the = sign before the between and adding a space between the values

*****
Also, in the report try
=Sum(IIf([InvDate] Between #1/1/07# AND #3/31/07#,1,0))

So it will add 1 when the date meet the criteria


--
Good Luck
BS"D

Please forgive the repost - someone supplied answer for running sum over
report pages and I only have one page and would like a text box with total
count for each quarter. I replied but no one picked up msg again.

I have a report broken down by quarter grouped by salesperson. I'm trying to
count the number of invoices per salesperson for each quarter. I thought I
could put a text box under each quarter with count or dcount expressions - so
far everything ends in "error" or same wrong total for each salesperson. I've
tried different variations on the following control source for textbox:
DCount("[Inv]","table1","[InvDate]=Between#1/1/07#AND#3/31/07#") along with
several Count expressions.

If someone would supply the correct expression, I would certainly appreciate
it.
Thanks,
Pam

 

[KARL DEWEY]

Try this --
SELECT Table1.salesperson, Format([InvDate],"yyyy q") AS Year_QTR,
Count(Table1.Inv) AS Invoices
FROM Table1
GROUP BY Table1.salesperson, Format([InvDate],"yyyy q");