I ve 2 tables, one for customer data and d other for transactions. what i want to do is dt , i want to generate  their current balances which i ve done.but my challenge is, i want to list also balances that are greather than or less that a specified value which i ve d free will to change at anytime on the form. pls how do i do this? let me give d structure of the 2 tables and a sapmle data so dt u know what d table is like.

 

customer data table has

 

customerid   customer name

1000              Wesley Snipes

1001              Harrison Ford

1002              Samuel L. Jakson

1003              Brad piit

 

the transaction table has

 

customerid   transaction_description         debit                     credit

1000                     withdraw by self          2000.00                   0.0

1000                     deposit by self                0.0                     10000.00

1001                      deposit by self               0.0                        500.00

.

.

.

.

and like that.

what i did to get the balance is that i created a formula field that sum credit - debit and it worked fine. but from my program now to filter for those dt are > or < or = are not just working. In my code i use Sql query to pass the data to Crystal cos i use dynamic tables.what i noticed is that i can use selection fourmular to do that like if the balance field is @mybalance then i can say cry.selectionformular = 400">{@mybalance>400} as d case may be. but the moment i use Sql query in the same statement like cry.SQLQuery = "......." , the selection formular will not work but if i removed the .SQLQuery , it will work perfectly but this is not helping me at all. pls help me, i believe all these explainations will rely guide in giving solutions.

thanks

 

 

 

 

 

 

mSql = "SELECT  accountsubitems1.`accountsubitemname`,mastertransactions200981.`refnumber`, mastertransactions1.`debit`, mastertransactions200981.`credit`, mastertransactions1.`description`," & _
                    "biodatatable1.`accountnumber`, biodatatable1.`surname`, biodatatable1.`othernames`, biodatatable1.`accountname` From (`accountsubitems` accountsubitems1 INNER JOIN `" & mastertransactions & "` mastertransactions200981 ON " & _
                    " accountsubitems1.`accountsubitemid` = mastertransactions200981.`transactionaccountid`)  Left JOIN `biodatatable` biodatatable1 ON mastertransactions200981.`accountid` = biodatatable1.`accountid` Where " & _
                    " mastertransactions200981.transactiondate <= '" & txtdate(3).Text & "'" & xSql & Chr$(13) + Chr$(10) + "Order By accountsubitems1.`accountsubitemname` ASC"

 

this query worked fine. but to get the balance of sum(credit - debit) tested for is now the problem. Pls help