Slow Reports when using DSUM and DLOOKUP in OnFormat event

[jsccorps]

Report takes several minutes to run, and slowness is not acceptable to users.

The report has several DSUMs and DLOOKUPs in the detail OnFormat event, and
a similar number of DSUMs and DLOOKUPs in the ReportFooter OnFormat event.
Are DSUMs and DLOOKUPs inherently slow? Would it be faster to eliminate the
DSUMs/DLOOKUPs from the report and do these calculations in a query? Any
suggestions would be appreciated.

 

[Duane Hookom]

DSum() and others are VERY slow and in-efficient in code, reports, and
queries. You should replace them wherever possible.

Since you didn't tell us what types of calculations you are doing, we can't
provide alternatives.

 

[jsccorps]

Examples of calculations follow:

ManagerRate = ELookup("[Rate]", "[MonthlyRates]", "[Manager#]=" & [Manager],
"RateDate DESC")

RebateBeginBalance = DLookup("[RebateBegin]", "[Monthly Cash Card-Beginning
Balances]", "[Manager #]=" & [Manager])

RebateDeposits = DSum("[Rebate]", "[Monthly Cash Deposit Amounts]", "[Sales
Date] <= #" & [SalesDate] & "# and [Manager #]=" & [Manager])

RebatePayoutPreviousBal = Nz(DSum("[Rebate]", "[Monthly Cash Request]",
"[Sales Date] <= #" & DateSerial(Year([SalesDate]), Month([SalesDate]), 0) &
"# and [Manager #]=" & [Manager]))

Hope this helps

 

[Duane Hookom]

To get the RebateBeginBalance, why don't you include [Monthly Cash
Card-Beginning Balances] in your report's record source query and join the
[Manager #] and [Manager] fields? This would run much faster.

--
Duane Hookom
MS Access MVP
--

Examples of calculations follow:

ManagerRate = ELookup("[Rate]", "[MonthlyRates]", "[Manager#]=" &
[Manager],
"RateDate DESC")

RebateBeginBalance = DLookup("[RebateBegin]", "[Monthly Cash
Card-Beginning
Balances]", "[Manager #]=" & [Manager])

RebateDeposits = DSum("[Rebate]", "[Monthly Cash Deposit Amounts]",
"[Sales
Date] <= #" & [SalesDate] & "# and [Manager #]=" & [Manager])

RebatePayoutPreviousBal = Nz(DSum("[Rebate]", "[Monthly Cash Request]",
"[Sales Date] <= #" & DateSerial(Year([SalesDate]), Month([SalesDate]), 0)
&
"# and [Manager #]=" & [Manager]))

Hope this helps

DSum() and others are VERY slow and in-efficient in code, reports, and
queries. You should replace them wherever possible.

Since you didn't tell us what types of calculations you are doing, we
can't
provide alternatives.