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

[June7]

The DSum syntax looks correct, except for the oddball font of quote mark in front of Year1.



We are going in circles. If you can't provide db for analysis, I won't be able to help further.

[Hardrock]

Update on this thread and new question.....THANKS TO JUNE7 FOR ALL THE HELP. I FINALLY GOT THIS WORKING. Almost!

With some local help, we found that everything June said should have worked but it seems the WAY I was entering the code was affecting performance. I ended up using this:

=DSum("[check] + [currency] + [inkind]","money","Year(date1) = " & [Forms]![Organization Details]![Year1] & " AND ContributorID=" & [ContributorID])

Which is, I think, exactly what June suggested....only difference is we highlighted the DSUM box, clicked the ellipse, and CHOSE the fields, forms, etc from a list rather than typing them in. There may some slight difference but I haven't looked for it yet.

Anyway....new question. While testing this, I noticed that some of my contributions didn't show up in the report and it seems to be based on the date of the contribution. My report dates currently are 12/10/2013 to 6/20/2014. I have one contributor with two contributions - one of $77 and one of $1.31 (both checks) If the date of the $77 contribution is 4/15/2014, the $77 shows up in the report but if I change the month and the date is 1/15/2014, the $77 doesn't show up ALTHOUGH THE DSUM STATEMENT (for the $1.31 contribution) STILL WORKS AND THE TOTAL FOR 2014 IS $77 + $1.31 OR $78.31. Change date of the $77 contribution back to 4/15/2014 and the $77 is back in the report and the DSUM for the both the $77 and the $1.31 contribution correctly shows $78.31.

It's like some dates work and some don't....any ideas? My next move is to delete the contributor altogether and start over but if this happens once it could affect my ability to enter new contributions for other contributors and I need to understand what's happening.

Further testing has led me to conclude that the date problem appears to be related to any date that starts with January. If I put in 1/xx/14, it doesn't work. change the 1 to any other month and it's good.

[June7]

The DSum still works because it is not using the report RecordSource as data source, it pulls data directly from table or query object.

Something about the report is filtering out the records.

Again, would need to examine db and data.