Calculating totals on report.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I've a report which needs to calculate a period to date figure depending on
the period number which appears on it.

I've tried using an iif expression, but it's far too long and cannot display.

Can anyone help?
 
Do you have a table/query structure you would like to share as well as some
sample records?
 
You may have better luck with a DSum() function based on the report's record
source. You would use the date period on the report for the Where part
filter of the DSum() function.

Example:
=DSum("[MyField]", "[ReportControlSourceName]", "[DateField]>= #" &
[ReportStartDate] & "# And [DateField] <= #" & Date() & "#")
 
At the moment, my sql reads as follows:

SELECT dbo_MSLiveDetailedTB.trans_period, dbo_MSLiveDetailedTB.nlyear,
dbo_MSLiveDetailedTB.nominal_code, dbo_MSLiveDetailedTB.description,
dbo_MSLiveDetailedTB.journal_date, dbo_MSLiveDetailedTB.journal_number,
dbo_MSLiveDetailedTB.journal_desc, dbo_MSLiveDetailedTB.journal_amount,
dbo_MSLiveDetailedTB.base_amount01, dbo_MSLiveDetailedTB.base_amount02,
dbo_MSLiveDetailedTB.base_amount03, dbo_MSLiveDetailedTB.base_amount04,
dbo_MSLiveDetailedTB.base_amount05, dbo_MSLiveDetailedTB.base_amount06,
dbo_MSLiveDetailedTB.base_amount07, dbo_MSLiveDetailedTB.base_amount08,
dbo_MSLiveDetailedTB.base_amount09, dbo_MSLiveDetailedTB.base_amount10,
dbo_MSLiveDetailedTB.base_amount11, dbo_MSLiveDetailedTB.base_amount12,
dbo_MSLiveDetailedTB.period_actual01, dbo_MSLiveDetailedTB.period_actual02,
dbo_MSLiveDetailedTB.period_actual03, dbo_MSLiveDetailedTB.period_actual04,
dbo_MSLiveDetailedTB.period_actual05, dbo_MSLiveDetailedTB.period_actual06,
dbo_MSLiveDetailedTB.period_actual07, dbo_MSLiveDetailedTB.period_actual08,
dbo_MSLiveDetailedTB.period_actual09, dbo_MSLiveDetailedTB.period_actual10,
dbo_MSLiveDetailedTB.period_actual11, dbo_MSLiveDetailedTB.period_actual12,
dbo_MSLiveDetailedTB.budget_code,
IIf(Forms!Selection!Periods=1,[base_amount01],IIf(Forms!Selection!Periods=2,[base_amount02],IIf(Forms!Selection!Periods=3,[base_amount03],IIf(Forms!Selection!Periods=4,[base_amount04],IIf(Forms!Selection!Periods=5,[base_amount05],IIf(Forms!Selection!Periods=6,[base_amount06],IIf(Forms!Selection!Periods=7,[base_amount07],IIf(Forms!Selection!Periods=8,[base_amount08],IIf(Forms!Selection!Periods=9,[base_amount09],IIf(Forms!Selection!Periods=10,[base_amount10],IIf(Forms!Selection!Periods=11,[base_amount11],IIf(Forms!Selection!Periods=12,[base_amount12],""))))))))))))
AS Exp1,
IIf(Forms!Selection!Periods=1,[period_actual12],IIf(Forms!Selection!Periods=2,[period_actual01],IIf(Forms!Selection!Periods=3,[period_actual02],IIf(Forms!Selection!Periods=4,[period_actual03],IIf(Forms!Selection!Periods=5,[period_actual04],IIf(Forms!Selection!Periods=6,[period_actual05],IIf(Forms!Selection!Periods=7,[period_actual06],IIf(Forms!Selection!Periods=8,[period_actual07],IIf(Forms!Selection!Periods=9,[period_actual08],IIf(Forms!Selection!Periods=10,[period_actual09],IIf(Forms!Selection!Periods=11,[period_actual10],IIf(Forms!Selection!Periods=12,[period_actual11],"")))))))))))) AS Exp2
FROM dbo_MSLiveDetailedTB
WHERE (((dbo_MSLiveDetailedTB.trans_period)=[Forms]![Selection]![Periods])
AND ((dbo_MSLiveDetailedTB.nlyear)=[Forms]![Selection]![Years]));

What I am trying to achieve is this for example......
if forms!Selection!periods=2 then [period_actual01]+[period_actual02]
going through for period 3 would be period01+02+03 etc..
 
I would use a union query to normalize the data. You could then quite easily
sum values by month/period.

--
Duane Hookom
MS Access MVP


Lynne said:
At the moment, my sql reads as follows:

SELECT dbo_MSLiveDetailedTB.trans_period, dbo_MSLiveDetailedTB.nlyear,
dbo_MSLiveDetailedTB.nominal_code, dbo_MSLiveDetailedTB.description,
dbo_MSLiveDetailedTB.journal_date, dbo_MSLiveDetailedTB.journal_number,
dbo_MSLiveDetailedTB.journal_desc, dbo_MSLiveDetailedTB.journal_amount,
dbo_MSLiveDetailedTB.base_amount01, dbo_MSLiveDetailedTB.base_amount02,
dbo_MSLiveDetailedTB.base_amount03, dbo_MSLiveDetailedTB.base_amount04,
dbo_MSLiveDetailedTB.base_amount05, dbo_MSLiveDetailedTB.base_amount06,
dbo_MSLiveDetailedTB.base_amount07, dbo_MSLiveDetailedTB.base_amount08,
dbo_MSLiveDetailedTB.base_amount09, dbo_MSLiveDetailedTB.base_amount10,
dbo_MSLiveDetailedTB.base_amount11, dbo_MSLiveDetailedTB.base_amount12,
dbo_MSLiveDetailedTB.period_actual01,
dbo_MSLiveDetailedTB.period_actual02,
dbo_MSLiveDetailedTB.period_actual03,
dbo_MSLiveDetailedTB.period_actual04,
dbo_MSLiveDetailedTB.period_actual05,
dbo_MSLiveDetailedTB.period_actual06,
dbo_MSLiveDetailedTB.period_actual07,
dbo_MSLiveDetailedTB.period_actual08,
dbo_MSLiveDetailedTB.period_actual09,
dbo_MSLiveDetailedTB.period_actual10,
dbo_MSLiveDetailedTB.period_actual11,
dbo_MSLiveDetailedTB.period_actual12,
dbo_MSLiveDetailedTB.budget_code,
IIf(Forms!Selection!Periods=1,[base_amount01],IIf(Forms!Selection!Periods=2,[base_amount02],IIf(Forms!Selection!Periods=3,[base_amount03],IIf(Forms!Selection!Periods=4,[base_amount04],IIf(Forms!Selection!Periods=5,[base_amount05],IIf(Forms!Selection!Periods=6,[base_amount06],IIf(Forms!Selection!Periods=7,[base_amount07],IIf(Forms!Selection!Periods=8,[base_amount08],IIf(Forms!Selection!Periods=9,[base_amount09],IIf(Forms!Selection!Periods=10,[base_amount10],IIf(Forms!Selection!Periods=11,[base_amount11],IIf(Forms!Selection!Periods=12,[base_amount12],""))))))))))))
AS Exp1,
IIf(Forms!Selection!Periods=1,[period_actual12],IIf(Forms!Selection!Periods=2,[period_actual01],IIf(Forms!Selection!Periods=3,[period_actual02],IIf(Forms!Selection!Periods=4,[period_actual03],IIf(Forms!Selection!Periods=5,[period_actual04],IIf(Forms!Selection!Periods=6,[period_actual05],IIf(Forms!Selection!Periods=7,[period_actual06],IIf(Forms!Selection!Periods=8,[period_actual07],IIf(Forms!Selection!Periods=9,[period_actual08],IIf(Forms!Selection!Periods=10,[period_actual09],IIf(Forms!Selection!Periods=11,[period_actual10],IIf(Forms!Selection!Periods=12,[period_actual11],""))))))))))))
AS Exp2
FROM dbo_MSLiveDetailedTB
WHERE (((dbo_MSLiveDetailedTB.trans_period)=[Forms]![Selection]![Periods])
AND ((dbo_MSLiveDetailedTB.nlyear)=[Forms]![Selection]![Years]));

What I am trying to achieve is this for example......
if forms!Selection!periods=2 then [period_actual01]+[period_actual02]
going through for period 3 would be period01+02+03 etc..

Duane Hookom said:
Do you have a table/query structure you would like to share as well as
some
sample records?
Click to expand...
Click to expand...
 
Back
Top