If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
count records by date range
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 |
#2
|
|||
|
|||
count records by date range
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 "PHisaw" wrote: 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 |
#3
|
|||
|
|||
count records by date range
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 "Ofer Cohen" wrote: 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 "PHisaw" wrote: 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 |
#4
|
|||
|
|||
count records by date range
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 "Ofer Cohen" wrote: 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 "PHisaw" wrote: 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 |
#5
|
|||
|
|||
count records by date range
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"); -- KARL DEWEY Build a little - Test a little "PHisaw" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|