modify an expression

[Amin]

hi i have this account transaction database template from microsoft, and i
want to modify it the transactions extended query to also subtract savings in
addition to the expenses category.

when i checked the query it had this expression. Actual Amount:
IIf([Categories].[Income/Expense]="Expense",-([Account
Transactions]![Transaction Amount]),[Account Transactions]![Transaction
Amount])

now al i want is that it should also subtract a new categofy i want to put
in the category list within the combo drop down list which will be called
Standing Order-Savings.

what i intend to acheive is when i enter data that is expenses such as
Dining Out it is subtracted from my total. so i want the savings category
also be subtract from the total amount but it should not be under expenses.
so i did this by adding savings to the expression

Actual Amount:
IIf([Categories].[Income/Expense]="Expense","Savings"-([Account
Transactions]![Transaction Amount]),[Account Transactions]![Transaction
Amount]) but the results never worked right.

what should i do the expression to get this results any ideas will be
welcome. and again i will want to know if i need to do any modifications to
the tables and queries befor this can be effective.

 

[Marshall Barton]

hi i have this account transaction database template from microsoft, and i
want to modify it the transactions extended query to also subtract savings in
addition to the expenses category.

when i checked the query it had this expression. Actual Amount:
IIf([Categories].[Income/Expense]="Expense",-([Account
Transactions]![Transaction Amount]),[Account Transactions]![Transaction
Amount])

now al i want is that it should also subtract a new categofy i want to put
in the category list within the combo drop down list which will be called
Standing Order-Savings.

what i intend to acheive is when i enter data that is expenses such as
Dining Out it is subtracted from my total. so i want the savings category
also be subtract from the total amount but it should not be under expenses.
so i did this by adding savings to the expression

Actual Amount:
IIf([Categories].[Income/Expense]="Expense","Savings"-([Account
Transactions]![Transaction Amount]),[Account Transactions]![Transaction
Amount]) but the results never worked right.

what should i do the expression to get this results any ideas will be
welcome. and again i will want to know if i need to do any modifications to
the tables and queries befor this can be effective.

Your approach is starting to get messy and I hope you never
add another category.

Other than redsigning your tables to avoid all these special
conditions, I guess you can try using something like:

Actual Amount:
IIf([Categories].[Income/Expense] IN("Expense","Savings"),
-[Account Transactions]![Transaction Amount],
[Account Transactions]![Transaction Amount])

Maybe(?) you would be better off having another field that
just indicates if the amount is a debit or credit???