How to get Grand number while selecting a period? 2

[wdacc]

I have a report which shows both sum of every period and also year to date -Grand Total. The problem is when I select a period (I did this in a query)the grand total is same as sum of the period. How can I get Grand Total from Year To Date even I input a beginning date and ending date?
Thanks in advance !

 

[randysmid]

Hi,
If I understand correctly, you want the YTD total regardless of the period being selected. Is this correct?

If this is true, you can use the DSum function inside your report footer (or anyplace you choose to place it). To do this, simply add a textbox to the location you want, then add the DSum statement as follows for the recordsource of the textbox:

=DSum("[Sales_amt]","tblSales","[Sales_date] between #01/01/2003# and #12/31/2003#&quot

Presumptions:
The field containing the sales amount is called Sales_amt.
The table containing the sales figures is called tblSales.
The field containing the sales date is called Sales_date.

HTH,
Randy Smith
California Teachers Association

 

[wdacc]

Thank you very much, Randy. It's very useful. I'm pretty sure many, many people will be benificial from your reply.

 

[randysmid]

Hi,
I took this info and added more to it to create an FAQ on this topic. You can see it at: faq703-3066
HTH,
Randy Smith
California Teachers Association

 

[wdacc]

Hi, Randy

Your example selects table. I tried and found if I set two requirements in a query, it doesn't work. Would you give me some detail about it?

 

[randysmid]

HI,
I don't think I ever used a DSum (or any other "D" function) with anything than a table. Going to the table directly has a significant advantage: you will always get the asbolute latest information from the database.

Another technique - Use a text box with a query as its recordsource on the report. Many people don't realize it, but each control on a report can have it's own recordsource (including a query). BUT, you must make sure that the query can only return ONE VALUE (such as a "sum" query). If it returns multiple values, then I believe you will only get the first value, which may not be correct. With this technique, you do not need to use the DSum function. HTH,
Randy Smith
California Teachers Association