Adding Values in a Listbox

[bursteffect]

Hello everyone. im new to Access and willing to learn and someday be able to help someone. Here's the problem.. i need a code or someone to show me how to fix this problem...
im working on a personal expence ledger. I need some assistance on a particular field that is labeled [expence/income], and a field labeled [amount]. the [expence/income]field is a listbox field "with expence and imcome as the values. Here is what i want to do. when i select income from the listbox i want it to sum the total in the amount] field, and if i select expence i want it to subtract the total in the [amount] field. i hope i explain it correctly.

/Thanks Bobby

 

[Galaxiom]

You need to explain more about your database. What you are trying to do does not sound like a typical operation in a properly designed data structure.

BTW "Expense"

 

[CJ_London]

If I understand you correctly in terms of calculating the value for the line it would be amount*iif(listbox="expense",-1,1)

if you want the total in a form it would be
=dsum("amount*iif(listbox="expense",-1,1)","tblTrans")

and in a query you would have a subquery:

(SELECT Sum(amount*iif(listbox="expense",-1,1)) FROM tblTrans as tmp)

This give you a total from the beginning, if you want incremental balances like you see on a bank statement then you need to add some criteria - the easiest is if you have an incremental autonumberid then the critera would be WHERE ID<=record.id

so:
=dsum("amount*iif(listbox="expense",-1,1)","tblTrans","ID<=" & tbltrans.ID)

(SELECT Sum(amount*iif(listbox="expense",-1,1)) FROM tblTrans as tmp WHERE ID<=tbltrans.id)

 

[bursteffect]

what im trying to do is, lets say i have 3 fields. 1.amount,2.listbox with 2 selection "expense and income", 3 total. lets say i put 5 dollars in the amount field, then select the list box to income, i would like the total field to show 5.00$, if i select expense i would like the total field to show (5.00$)

 

[CJ_London]

Not totally clear on what you want to show in the total control

I'm assuming it is the value of the number of $5 in the tbltrans i.e. if there are 5 expense records it will shoe -$25

If you put in the total field control the following it should work

=dsum("amount*iif(listbox="expense",-1,1)","tblTrans","ExpType = '" & listbox & "'")

You'll need to change names as required

to get the figure to appear in red, you will need to use conditional formatting but so far as I am aware there is not an equivalent to Excel where you can put brackets around it. although you could set the control to

=iif(listbox="expense","(","") & dsum("amount*iif(listbox="expense",-1,1)","tblTrans","ExpType = '" & listbox & "'")
& iif(listbox="expense",")","")