SQL Code in Access Report

[KristynFelts]

I have the following code that is supposed to calculate Previous (fiscal) Year to date (report date) donors. However, this count is often greater than the total number of donors reported for the previous fiscal year, which is the same year as PYTD. Why is my donor count increasing, sometime doubling more than the actual total number of donors counted in the same fiscal year?

Me!PYTDAlumDonor.Value = ECount("[Constit_id]", "[PY Gifts]", "[Constituency] In ('Alumni')AND [DTE] BETWEEN #" & DateAdd("yyyy", -1, dFYStart) & "# AND #" & DateAdd("yyyy", -1, dFYEnd) & "#")

I think I need to change this from ECount to DCount but I'm having trouble making it work.

 

[MajP]

That is hard to debug with all the formatting and logic.
I would make a single query
qryDonorsPerYear
do an aggregate query where constituency = Alumni

Then your formula is dlookup("NumberDonors","qryDonorsPerYear","DonorYear = " & year(date) - 1)

 

[plog]

Agree with MajP and my guess as to where it is going wrong is that you are counting gifts, not donors. [PY Gifts] is most likely a list of gifts which means if a donor gave multiple gifts they are showing up multiple times in that table and you are counting them each time they gave a gift.

If you want unique donors regardless of number of gifts you need a query like MajP suggests.

 

[MajP]

My point was more about the ease of building, ease of error checking, and ease of use. Once you build your query you will immediately see the results and if they are not quite right you can easily modify the query. Then your call to the query is extremely simple using a dlookup and a given year.

 

[Ken Sheridan]

You'd need a query along these lines to return the number of distinct donors per accounting year:

SELECT
    AccountingYear,
    COUNT(*) AS DonorCount
FROM
    (
        SELECT DISTINCT
            AcctYear (4, 5, DTE) AS AccountingYear,
            Constit_id
        FROM
            [PY Gifts]
        WHERE
            Constituency = "Alumni"
    )
GROUP BY
    AccountingYear;

In this example the accounting year is the UK fiscal year beginning 5 April, and is returned in the format YYYY-YY with the following function:

Public Function AcctYear(MonthStart As Integer, DayStart As Integer, Optional DateVal As Variant) As String

    Dim dtmYearStart As Date
    
    If IsMissing(DateVal) Or IsNull(DateVal) Then DateVal = VBA.Date

    If MonthStart = 1 And DayStart = 1 Then
        ' accounting year is calendar year, so return single year value
        AcctYear = Year(DateVal)
    Else
        ' get start of accounting year in year of date value
        dtmYearStart = DateSerial(Year(DateVal), MonthStart, DayStart)
        
        ' if date value is before start of accounting year
        ' accounting year starts year previous to date's year,
        ' otherwise it starts with date's year
        If DateVal < dtmYearStart Then
            AcctYear = Year(DateVal) - 1 & Format(Year(DateVal) Mod 100, "-00")
        Else
            AcctYear = Year(DateVal) & Format((Year(DateVal) + 1) Mod 100, "-00")
        End If
    End If

End Function

You can then return the count of donors for any accounting year with a simple DLookup function call.

 

[MarkK]

If your report module supports class-global variables, these are not automatically reset between PrintPreview and Print. If, for instance, you show and hide Detail sections of your report based on some criteria your RecordSource is not aware of, and you have a ...

Private m_sum As Currency    ' report-global conditional sum

...variable that accumulates values, but only for visible Detail sections, m_sum might hold a non-zero value when PrintPreview completes, but more critically, it still holds a non-zero value when Print starts.
• This manifests as an intermittent doubling of values, because PrintPreview is correct on its own, and Print is correct on its own.

To solve this--if it is this--reset your report_global variables for each presentation, like ...

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
    ' clear report-global variable before each presentation
    m_sum = 0
End Sub

 

[Mark_]

I'll second MajorP's suggestion as it will make troubleshooting far easier. Create the query, look at the results, walk through your results to make sure they match what you NEED to count.