Query Referring to Previous Record Crashes Access

[jrub]

Hi All,

Can anyone help me clean up my query so that it doesn't crash access and cause the CPU usage to sky rocket?

I want to simply add together values from a previous line, and it worked fine for one column (though slow) using the dlookup feature. When I did it for another column with dlookup, access crashes...

Can anyone suggest how to accomplish this better?

SELECT AnalysisYears.StudyYears, [Funding Plan Ideal Balances].UnadjustedIdealBalance, [Funding Plan Ideal Balances].InflationAdjustedIdealBalance, [Funding Plan Summary Expenditures].Inflation_Adjusted_Expenditures, [Funding Plan Summary Expenditures].Expr1 AS UnadjustedExpenditures, ReserveParameters.InvestmentRate AS InvestRate, Format([ReserveParameters]![CashFlowStartingContribution]*(1+[ReserveParameters]![InvestmentRate])^([StudyYears]-[ReserveParameters]![StartingBalanceYear]),"Currency") AS AnnualContribution, [InvestRate]*([StartBalance]-[Inflation_Adjusted_Expenditures]/2+[AnnualContribution]/2) AS InterestIncome, IIf([StudyYears]=[ReserveParameters]![StartingBalanceYear],[ReserveParameters]![StartingBalance],DLookUp("[AnnualContribution]","GenerateFundingPlanData","[StudyYears]=" & [StudyYears]-1)+DLookUp("[InterestIncome]","GenerateFundingPlanData","[StudyYears]=" & [StudyYears]-1)) AS StartBalance
FROM ReserveParameters, [Funding Plan Summary Expenditures] INNER JOIN (AnalysisYears INNER JOIN [Funding Plan Ideal Balances] ON AnalysisYears.StudyYears = [Funding Plan Ideal Balances].StudyYears) ON [Funding Plan Summary Expenditures].CEY = AnalysisYears.StudyYears
ORDER BY AnalysisYears.StudyYears;

 

[boblarson]

The quick version is that I think you are ending up with the DLookup returning a NULL which it can't have. I probably would be doing this kind of differently instead of using DLookups which are slow (perhaps Alan Browne's ELookup) but the quick fix is to encapsulate the DLookup in the Nz function like this:

Nz(DLookUp("[InterestIncome]","GenerateFundingPlanData","[StudyYears]=" & [StudyYears]-1)),0) AS StartBalance

 

[jrub]

Trying it now...thanks

 

[boblarson]

By the way I forgot to include the link to the ELookup:

http://allenbrowne.com/ser-42.html

 

[jrub]

After reading Allen Brown's site, I should be using a subquery.

I'm quite the noob, but is this an easy subquery to construct?

 

[jrub]

How would one convert this into a subquery?

DLookUp("[AnnualContribution]+[InterestIncome]+[StartBalance]","GenerateFundingPlanData","[StudyYears]=" & [StudyYears]-1)

As I just want to add the three values from the previous record together?
I used an iif statement to judge if it was the first record or not.

 

[jrub]

Using Allen Browne site, I see I should first define each variable prior to any iif statements.

I started with the following:

[PriorAnnualContribution], (SELECT TOP 1 Dupe.PriorAnnualContribution FROM PriorAnnualContribution As Dupe WhereDue.StudyYears = GenerateFundingPlanData.StudyYears

But not quite understanding the rest of the SQL...any help out there?