Totals for records not included in report

[hilian]

In a report I need to calculate a total amount spent for a time period (a quarter) and the remainder available for the rest of the year. If I’m creating a report for the first or the last quarter, then there's no problem: simply subtract the amount spent from the amount budgeted for the year. The result is what’s left for the rest of the year. For the second and third quarters, I need to add the amount spent for past quarter(s) to what was spent for the quarter covered by the report and then subtract that amount from the amount budgeted. How do I do that?

[June7]

Options:

1. DSum expression in a textbox on report

2. Subreport

Don't you have to add the previous 3 quarters to the last quarter and subtract from budget to determine remainder?

[hilian]

I'm using a parameter in a query to limit the records selected. Won't DSum simply sum the records that the parameter specifies, or can I include records that go outside of the dates for the report?

You're right about point 3. I wasn't thinking clearly when I wrote the first and last quarters were no problem. Only the first quarter escapes this problem.

If DSum will allow me to specify records beyond the limitation of the parameter, then I'm good to go. Otherwise I guess a subreport will be the best option, but I'm trying to keep things simple.

For DSum, how would I include the end date from the parameter that defines the dates to include for the report? For that matter, if I don't want to hard code the begin date, how do I get that in as part of the criteria?

Many thanks,

Henry

[June7]

Parameters for domain aggregate functions (DSum, DAvg, DMin, DMax, etc) are independent of the query or form or report parameters. If you want them to synchronize, both must have the same criteria.

Maybe have the date parameter refer to a textbox on form as input? I just don't know enough about your structure.

[hilian]

How would the text box work? The report is for a quarter's worth of records from a table that now has a year's worth but will have more. I'm running the report off a query because it allows me to specify the parameter. I'm also using the query to calculate amounts spent per record. the records in the underlying table have dates, which I'm including in the query.

[June7]

You are using query input parameter prompt? I don't use that method because can't validate user input. I recommend user input criteria in controls on form then the query refers to the form controls as inputs. The form must remain open as the report renders.

Another method is to use the WHERE CONDITION argument of DoCmd.OpenReport. Use VBA code to construct the WHERE string by referencing the controls on form.

Show the query SQL statement.

[hilian]

User input on the form seems like the simplest way to do it. Does the user input come from text boxes bound to the date field on the query? Would the start date for computing the year's total also be a text box on the form, and would it be bound to the date field on the query? How would I get the beginning date for the fiscal year in without having the user input it, or is that the best way?

[June7]

Controls used strictly for criteria input would not be bound. Don't want to change the data in a record. Review http://datapigtechnologies.com/flash...mtoreport.html

Have user specify the fiscal year. That way can run report on Jan 1 2013 for 2012. (or on Jul 1 if your FY is Jul-Jun).

[hilian]

Thanks! The video was excellent. It really explained what I need. It seems that what I'm putting into the query statement is a reference to two text boxes with the start and end dates. Does the user specify the fiscal year in another text box? Is there a text box on the report that references the one on the form?

[June7]

So start and end dates won't necessarily be within a fiscal year period?

If you want additional criteria to further filter the records returned in the date range, have user input the FY. Might want some code to validate that the given date range would return records for the given FY.

Report textbox could reference control on form.

[hilian]

Start and end dates will almost always be within the fiscal year. The users may need some special report, but if I understand correctly, that should be possible by this apporach. The user will input the fiscal year, which will relate to the text box on the report with the DSum expression, and the user will also input the start and end dates which will be the criteris for the command button, right?

[June7]

Sounds like you got the idea. Do you want to require the start and end dates to both be entered before report runs?

[hilian]

The report will need start and end dates to get the correct quarter.

[June7]

Then might want to have code that will assure that inputs are valid before running report.

[hilian]

I thought of using input masks. The other options are to have a dropdown list with 1st quarter, 2nd quarter, etc., with the dates in a table. If I do that, I'm not sure how to get the year in without manually updating it every Jan 1. I know there's a better way than that. I'm using access 2003. I understand that from A2007 on you can have data entry from a pop-up calendar.