Report Subtotals

[Karl Burrows]

I have a report that creates subtotals for monthly sales, then another
footer for annual sales and finally a footer for grand totals. Right now,
the yearly subtotal will pull out each year, but how can I differentiate
between a partial year and a full year in the subtotal descriptions, It
just says for year 2005?

="Summary For " & Format([edtActualClosingDate],"yyyy") & "Lots Sold= " &
Count([LotNumber]) & "Average Lot Price= " &
Format(Avg([LotPrice]),"Currency") & "Total Lot Price = " &
Format(Sum([LotPrice]),"Currency")

Users are required to input a starting and ending date range, but if they
only put in 3/1/2005 to 6/30/2005, how can I tell the report to look at it
and if it is a full year (or year to date) to keep using current syntax and
if it is a partial year to say March 1 to June 30, 2005 for the footer
total? Or, if years overlap, it will show yearly totals (example: input
range 7/1/2004 to 6/30/2005 to show 7/1 to 12/31/2004 as a subtotal and 1/1
to 6/30/2005 as a subtotal)

Thanks!

 

[Ken Sheridan]

Rather than basing the definition of 'partial' on the dates you could do it
on the data itself by comparing the number of rows in the report for the year
with the number of rows for that year in the underlying table or query. In
the report's module add a function:

Function PartYear(lngCount as Long, intYear as Integer) As String

Dim strCriteria as String

strCriteria = "Year(YourDateField) = " & intYear

If lngCount < DCount("LotNumber","YourTableOrQuery", strCriteria) Then
PartYear = " (part year) "
End If

End Function

The ControlSource of the control in the footer would then call the function
like so, passing the year and the number of lots into it as its arguments, to
insert '(part year)' after the year if the number of rows for the year in
the report was less than that in the table or query:

="Summary For " & Format([edtActualClosingDate],"yyyy") &
PartYear(Count([LotNumber]), Format([edtActualClosingDate],"yyyy")) &
"Lots Sold= " &
Count([LotNumber]) & "Average Lot Price= " &
Format(Avg([LotPrice]),"Currency") & "Total Lot Price = " &
Format(Sum([LotPrice]),"Currency")

 

[Karl Burrows]

I am following the logic, but get an error message in the report. I created
a new module in the database and added the Function and then updated the
calculation for the footer and it doesn't seem to work.

Am I doing something wrong? Thanks!

Rather than basing the definition of 'partial' on the dates you could do it
on the data itself by comparing the number of rows in the report for the
year
with the number of rows for that year in the underlying table or query. In
the report's module add a function:

Function PartYear(lngCount as Long, intYear as Integer) As String

Dim strCriteria as String

strCriteria = "Year(YourDateField) = " & intYear

If lngCount < DCount("LotNumber","YourTableOrQuery", strCriteria) Then
PartYear = " (part year) "
End If

End Function

The ControlSource of the control in the footer would then call the function
like so, passing the year and the number of lots into it as its arguments,
to
insert '(part year)' after the year if the number of rows for the year in
the report was less than that in the table or query:

="Summary For " & Format([edtActualClosingDate],"yyyy") &
PartYear(Count([LotNumber]), Format([edtActualClosingDate],"yyyy")) &
"Lots Sold= " &
Count([LotNumber]) & "Average Lot Price= " &
Format(Avg([LotPrice]),"Currency") & "Total Lot Price = " &
Format(Sum([LotPrice]),"Currency")

 

[Ken Sheridan]

I see no obvious reason for the error at first glance. Check for things like
the names of your table or query and the date field not being correct in the
function. Then try debugging the code by setting a breakpoint at the
'strCriteria = "Year(YourDateField) = " & intYear' line and then open the
report and step into the code line by line to see if the error is in the
function's code. If not it suggests that there is something wrong with the
expression for the ControlSource property, though again nothing obvious
springs out.

Another way to check that the function itself is OK would be to enter:

? PartYear(42,2004)

in the debug window (aka immediate pane) where you know 42 is less than the
number of rows for that 2004 (if not use another number). It should return '
(part year) ' in the debug window.

Another thing to check is that the module and function haven't been given
the same name. I find this is done quite often. I always prefix module
names with 'bas'.