Slow Reports when using DSUM and DLOOKUP in OnFormat event

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

Guest

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.
 
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.
 
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
 
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
--

jsccorps said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top