DLookup / DMax Does Not Work Correctly

[schillc]

I can not get this query to use the DLookup / DMax function correctly. I get an error every single time. The IFF part of the statement works, due to me having a starting value of 100.

Can anyone help me out?

Beginning Bal: IIf([ID]=1,100,DLookup("[Ending Balance]","GMPSortKPIQuery","[ID]=" & [ID]-1))

GMPSortKPITableIDDateCustomerGross Pallets ReceivedPallets Processed18/31/20094121529/1/20094342539/2/200941006149/14/200942050059/15/200944540

GMPSortKPIQueryDateBeginning BalGross Pallets ReceivedPallets ProcessedEnding Balance8/31/20091001215979/1/2009#ERROR
3425#ERROR
9/2/2009#ERROR
10061#ERROR
9/14/2009#ERROR
20500#ERROR
9/15/2009#ERROR
4540#ERROR


SQL View for Query:
SELECT GMPSortKPITable.Date, IIf([ID]=1,100,DMax("[Ending Balance]","GMPSortKPIQuery","[ID]=" & [ID]-1)) AS [Beginning Bal], GMPSortKPITable.[Gross Pallets Received], GMPSortKPITable.[Pallets Processed], [Beginning Bal]+[Gross Pallets Received]-[Pallets Processed] AS [Ending Balance]
FROM GMPSortKPITable, [Fiscal Weeks]
WHERE (((GMPSortKPITable.Date) Between [Fiscal Weeks]![Begin Date] And [Fiscal Weeks]![End Date]));

 

[namliam]

Dup:
http://www.access-programmers.co.uk/forums/showthread.php?t=179590