I need to calculate running balance on data containing Null, which is sorted by few columns.
As example below, Clr is sorted by Null Desc, then by date, then just by ID.
So far, I just come out with this...

SELECT tr1.Clr, tr1.ID, tr1.Date, tr1.Acc, tr1.Dbt, tr1.Cdt, (SELECT SUM(Nz(tr2.Dbt) - Nz(tr2.Cdt)) FROM tbl00 AS tr2 WHERE (tr2.Acc = tr1.Acc AND tr2.Clr <= tr1.Clr)) AS Bal
FROM tbl00 AS tr1
WHERE (((tr1.Acc)=12345) AND ((tr1.Audit)=True))
ORDER BY tr1.Clr, tr1.Date, tr1.ID;

this query can give me the running balance for all data in Clr column not NULL, but when it come to null, the calculation stopped.

Clr Date ID Acc Dbt Cdt Bal
----------------------------------------------------------------------------------------------------------------
1 15/10/08 503 12345 100.00 NULL 100.00
2 14/10/08 504 12345 NULL 3.00 97.00
3 16/10/08 499 12345 NULL 150.00 -53.00
4 16/10/08 505 12345 200.00 NULL 147.00
5 18/10/08 506 12345 NULL 2.00 145.00
NULL 18/10/08 500 12345 1.00 NULL 146.00
NULL 19/10/08 499 12345 NULL 5.00 141.00

i was also trying to break the result into 2 sql which 1 as above, calculate all the non-NULL result and another sql to calculate the remaining that contain NULL in Clr. In these tries, i trying to add a sequence column to replace the NULL in Clr column, but also unsuccessful.

Any one can give me a hand???

Perhaps I am missing something. What about having the Dbt and Cdt fields default to 0 or using Nz(tr2.Dbt,0)?

Perhaps I am the one who miss something, some more detail in my question.
Actually, Nz is not the problem here. The calculation of BAL can be generate correctly, for the data where CLR is not NULL, but it stop after CLR reach NULL (since I sort Null value DESC). After CLR is NULL, I let the data sorted by DATE, then by ID. For this I try to add argument in (tr2.Acc = tr1.Acc AND tr2.Clr <= tr1.Clr) to continue the calculation, but of course, I failed.....