Calculations on Report that use a parameter query to select year and period

[Hardrock]

Out of curiosity I created a textbox in the report and entered your code above:

=DSum("[check] + [currency] + [inkind]", "money", "Year(date1) = " & [enter year])

It returned the total for ALL contributors for that year.....not the total for each contributor.

for example if Jim contributes $50 and $100 in 2014.
And Betty contributes $25 in 2014

The DSUM code SHOULD return:
Jim's total of $150


Betty's total of $25

Instead I'm getting
Jim - $175
Betty - $175

I realize my limited knowledge may be causing me to ask the wrong questions or to explain my goals incompletely.....am I going to be able to get the desired results with DSUM? Thanks for your patience!

[June7]

Include as many criteria in the DSum as needed. Assuming there is a field in the report for contributor ID:

=DSum("[check] + [currency] + [inkind]", "money", "Year(date1) = " & [enter year] & " AND ContributorID=" & [ContributorID])

[Hardrock]

YEAH!!!!

I almost wrote back before trying it because I thought your code was going to ask me to input the contributor ID.....then I just put in in there and voila`, IT WORKED!!!!!!

On first blush, this appears to do exactly what I want. Thanks for hanging in there with me!!!!!!!

[Hardrock]

The suggestion you made above, worked fine but I am now trying to do this with a form rather than with a parameter query. The form includes fields for the user to enter the beginning date, ending date, and year.

These three things are stored in a table called coverpage in fields called "periodfrom", "periodto", and "year1". The form I use to enter the data is called "organization details".

Now that I'm using the form method, I have had no luck altering the DSUM statement you gave me to work with the form. I've tried:

=DSum("[check] + [currency] + [inkind]","money","Year1 & " AND ContributorID=" & [ContributorID])
and
=DSum("[check] + [currency] + [inkind]","money", forms![organization details]!year1 & " AND ContributorID=" & [ContributorID])

I just get #name? in the field that is supposed to sum all the contributions for the entire calendar year. Any suggestions?

[Hardrock]

Also just tried this:
=DSum("[check] + [currency] + [inkind]","money","Year(year1) = " & [Forms]![organization details]![year1] & " AND ContributorID=" & [ContributorID])

[June7]

Do you mean the form has textboxes (not fields) for user to input criteria? These textboxes are UNBOUND? Controls used just for inputting filter criteria must be UNBOUND.

Does year1 field have a full date value?

[Hardrock]

Do you mean the form has textboxes (not fields) for user to input criteria? These textboxes are UNBOUND? Controls used just for inputting filter criteria must be UNBOUND.

Does year1 field have a full date value?

The form *was* based on fields. I just made the textboxes (those for collecting periodfrom, periodto, and year1) UNBOUND.

Can/should the other textboxes that are for entering data into a table be fields or unbound textboxes?

Year1 is only for yyyy.

So, having made the change to UNBOUND, what should the DSUM statement look like to achieve the same result that you got for me (above) in this statement which was based on the fact that I was originally using a parameter query to get starting date (periodfrom), ending date (periodto), and year1 (in yyyy)?

Here's what worked with the parameter query: =DSum("[check] + [currency] + [inkind]", "money", "Year(date1) = " & [enter year] & " AND ContributorID=" & [ContributorID])

N.B. - Important to remember that "date1" is the date of the contributions in mmddyyyy. "Year1" is the yyyy that I use to calculate the CUMULATIVE contributions for the calendar year.

[June7]

If year1 field has only year value then why use Year() function on year1?

Okay, date1 in the Year() function makes more sense.

If controls are for entering data into record, they should be bound to fields, unless you really want to use UNBOUND forms and I don't recommend that for a novice developer.

Controls used to input filter/search criteria should be UNBOUND.

I am not sure what the issue is. If you don't want input popup prompt, then reference control on form for parameter. Instead of [enter year], [control name here].

[Hardrock]

You're right....the issue is I don't want input popup prompt but I'm not sure what control name would be since the unbound text box on the form has no name/identifier/control source. The control source under the "DATA" tab on the property sheet for the unbound textbox is empty.

You said, "reference control on FORM for parameter"

The record source (same as your use of "control on form?) is below.....

SELECT CoverPage.County, CoverPage.[Political Party Affiliation], CoverPage.[KREF filer #], CoverPage.[Committee Chair], CoverPage.ChairAdd2, CoverPage.ChairCity, CoverPage.ChairState, CoverPage.ChairZip, CoverPage.ChairDayPhone, CoverPage.ChaircellPhone, CoverPage.ChairhomePhone, CoverPage.ChairEmail, CoverPage.Treasurer, CoverPage.TreasAdd1, CoverPage.TreasAdd2, CoverPage.TreasCity, etc etc etc and it does include Coverpage.year1

Above, you said "instead of [enter year], use [control name here]......is that [control name here] supposed to be a field name on the coverpage table or the record source of the entire form?

[June7]

I am totally lost. What do you mean the textbox does not have a name?

Post 8 describes using reference to textbox as parameter for DSum(). Whether or not the textbox (or combobox or listbox) is bound to field depends on purpose of the control.

Exactly what is the issue now?

[Hardrock]

Previously, I used a parameter query (popup input boxes) to let the user specify startdate (mmddyyyy), enddate (mmddyyyy), and year1 (yyyy). With your help this worked fine.

You said parameter queries are bad practice and that I should use a form instead. I'm trying to do that. My form (organization_details) now has unbound text boxes for startdate, enddate, and year1. I thought the purpose was so the user would enter this data on the form and they would NOT get the popup parameter queries. I thought that somehow the report was going to get startdate, enddate and year1 from the form.

Because these textboxes are unbound, when I click on them so they are highlighted with a yellow border, the property sheet DATA tab is blank next to control source. Your explanation above instructs me to use the [Control Name Here]. I thought you meant the control source for the unbound text box on the form but it is blank so I must be misinterpreting your instructions.

You said Post 8 describes using reference to textbox as parameter but I don't see how to reference a textbox with no name/control source. Elementary to you....inscrutable to me.

[June7]

Every thing has a name. You even stated in post 11 that you named a textbox as tbxYr. Even if control is not bound (no ControlSource) it has a name. So when I say [Control Name Here], I mean the control's Name, not its ControlSource.


Oh, and my only compensation is the satisfaction gained from helping.

[Hardrock]

I can only say you have the patience of Job. I too, provide tech support (obviously not in Access :-) and I would long ago have advised myself to go to the mountaintop and spend a year learning this stuff. Learn to walk before running, I would say.

Anyways....uh, OK the textbox has "year1" in the dropdown box below the headers "Property Sheet - selection type textbox" so it makes sense that is the "name".

So now I have this DSUM in the report textbox that is supposed to calculate my contribution totals for the year:
=DSum("[check] + [currency] + [inkind]","money","Year(date1) = " & [year1] & " AND ContributorID=" & [ContributorID])

Note that I did as instructed with "year1" in place of [enter year].......and when I run the report, it shows a popup box asking for year1. I have filled out the form and saved everything and it still asks for year1.

[June7]

The syntax for expression in report textbox that refers to a form textbox is in posts 8 and 12.

However, if the report is already filtered to a specific year, the report textbox does not need to refer to form textbox. The year is available in the report dataset and can reference that field, just like with ContributorID.

=DSum("[check] + [currency] + [inkind]","money", "Year(date1) = " & Year(date1) & " AND ContributorID=" & [ContributorID])

[Hardrock]

I tried several variations last one shown below.
=DSum("[check] + [currency] + [inkind]","money", “Year1 = " & forms![organization details]!year1 & " AND ContributorID=" & [ContributorID])

With each attempt, I got: "the expression you entered has an invalid string"

You may recall that I have to report individual contributions based on the date of the contribution ("date1" in mmddyyyy), I also have to run report for a period like 12/10/2013 to 1/6/2014. My form includes unbound text boxes to put in those dates as "periodfrom" and "periodto". Finally, I have to total ALL contributions for a calendar year. Users enters that year yyyy in a textbox called "year1". I POINT THIS OUT JUST TO EMPHASIZE THAT I HAVE BOTH DATE1 AND YEAR1 WITH WHICH I'M DEALING. With all this in mind, any idea why the DSum above doesn't work?