DLookup results in #Error *for 1st record only*

[skydivetom]

Query "qry_SampleData" includes two expressions:

1. EOM_BALANCE: (Select Sum(T.CHARGE) From tbl_SampleData As T Where  T.ID<=tbl_SampleData.ID;)+((Select Sum(T.CHARGE) From tbl_SampleData As T Where  T.ID<=tbl_SampleData.ID;)*0.065/12)
2. BALANCE: Val(DLookUp("[EOM_BALANCE]","qry_SampleData","[ID]=" & [ID]-1))

The EOM balance is calculated correctly. However, the BALANCE expression results in an "#Error" **for the 1st record** (only).

My questions:
a. Given there's no "previous record" for the 1st record, how can the BALANCE expression be tweaked so that it would pass the [CHARGE] field (i.e., $5,000 in this case) into the 1st record and then continues with the EOM balance calculation from the previous record (for all remaining records)?
b. How can the expression be limited to only 2 decimals.

Thanks,
EEH

[pbaldy]

Wrap the DLookup() in the Nz() function. I suspect the Val() function is throwing an error on the Null being returned.

[skydivetom]

pbaldy - thank you... the following appears to work.

BALANCE: Val(Nz(DLookUp("[EOM_BALANCE]","qry_SampleData","[ID]=" & [ID]-1),[CHARGE]))

Much obliged.

[pbaldy]

Happy to help!