DLookup / DMax Does Not Work Correctly

I can not get the DMax / DLookup function to work correctly. The IFF statement that I used works due to me having the startign value of 100. Can anyone please 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


Query SQL View:
SELECT GMPSortKPITable.Date, IIf([ID]=1,100,DLookUp("[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]));

You cannot do a DLookup in the same query, more over your better off not using a DLookup all together but rather doing a left outer join to get this data.