Form Query IIF Statement: Help Needed Please (1 Viewer)

[ans]

I am working on creating a database and I am stuck with creating an IIF statment that is needed. All help is much appreciated.
For an example of what I am working with I am including two screenshots.

I am trying to creat a IIF statment that looks at "Income/Expense" if it is an expense (aka 2 for the Income/ExpenseFK) then take the Transaction Amount and show it as negative in the ActualAmount text box control. If it is an income then just show it as a postive number.

The IIF statment will be in the Query: in the Field area. "ActualAmount: IIF(.......)

Thanks - Sincerely,
ans

Here is the form:

Here is the Query for the form as seen above.

 

[theDBguy]

Hi. Welcome to AWF!

What have you tried so far? Have you tried something like?

IIf(ExpenseType=2,-1*TransAmount,ActualAmt)

 

[Gasman]

FWIW I stored my expense items as negative anyway, so then when calculating balance, it is just a simple sum equation?

 

[ans]

Hi. Welcome to AWF!

What have you tried so far? Have you tried something like?

IIf(ExpenseType=2,-1*TransAmount,ActualAmt)

I have tried many different statments. I got the idea to do it from the Personal Account Ledger Template in Access. Even using the IIF statment expression format they use I get an error (#ERROR) returned.

I have attached a copy of the database I am working on.

---
Thanks

 

[MajP]

ActAmount: IIf([tblCategories.income/Expensefk]=2,-1*[transactionAmount],[TransactionAmount])

 

[ans]

ActAmount: IIf([tblCategories.income/Expensefk]=2,-1*[transactionAmount],[TransactionAmount])

Thank you very VERY much. I had that I was just missing the * in the statment. Now I just got to get it formated to look right.

---
Again Thanks Very Much.

 

[plog]

Your tables are incorrect, you need to fix those before moving on to your forms. The big issue I see is you've over normalized your tables. You've tried to put every single field in its own table. That is incorrect.

tblIncomeExpense is a great example. You've taken a boolean value (a field with just 2 values) and made a table for it. Instead you could just have a field for it in the table you are storing the foreign key. Better yet, you use positive and negative numbers in the Amount field to designate this.

That's not the only over normalization you've done. The rule I use is that if a table will have only 1 real field of data (autonumbers are not real data) then you do not use a table for it. That means you should not have all these tables:

tblZipCode, tblCity, tblState, tblAccountListType, tblExpenseType, tblCurrencyType

All those fields have just 1 real field in them. Instead you store the value of that real piece of data in the field you are storing its foreign key in. In tblAddress you would store the actual City, State and Zip code values, not the foriegn key to those irrelavant tables.

Lastly, i think you might have a bunch of circular relationships. Looking at your relationship tool I can draw multiple paths between tables, that is incorrect. There should only be 1 way to travel from 1 table to another in the Relationship Tool. For example, from tblBankInformation to tblAccountTransactions I trace 3 unique paths (directly, via tblBankAccountType, via tblBankAccountType-tblBankAccountsNumbers). Those too should be fixed prior to working on forms.

 

[theDBguy]

Thank you very VERY much. I had that I was just missing the * in the statment. Now I just got to get it formated to look right.

---
Again Thanks Very Much.

Hi. Glad to hear you got it sorted out. Good luck with your project.