Good day!
I just need a little help regarding my query..I need to get the opening balance by subtructing debit and credit column where date is less than fromdate(datepicker). But sometimes if the fromdate is a date without transaction, it still displays opening balance? I cant figure out whats the problem..My code below and table definition attached..
SQL = "SELECT" & _
" (SELECT iif(isnull(Sum(transamount)),0, Sum(transamount)) FROM tbl_transmaster A WHERE A.trannature='Dr' And A.accountID = '" & AccnTID & "' And A.transDate < #6/1/2015#) As Debit," & _
" (SELECT iif(isnull(Sum(transamount)),0, Sum(transamount)) FROM tbl_transmaster B WHERE B.trannature='Cr' And B.accountID = '" & AccnTID & "' And B.transDate < #28/2/2015#) As Credit," & _
" (Debit - Credit) As OptBalance " & _
" FROM tbl_transmaster"
rs_optbal.Open SQL, scnn, adOpenStatic, adLockOptimistic, adCmdText