1. ## FORMULA help (Access2k)

Hi guys,
I need to write a formula similar to the one below:
tt: IIf([Transaction Type]="MISCINC" And [quantity]<0,"MISCEXP",[Transaction Type])

If Transaction Type is MiscInc and [quantity] is a negative number then transaction type = MiscExp else its Transaction type.

Is the above formula right?? coz it doesn't seem to work.
<img src=/S/bow.gif border=0 alt=bow width=15 height=15>

2. ## Re: FORMULA help (Access2k)

Princess,

Did all the [quantity] have a number ? Otherwise you have to use Nz

tt: IIf([Transaction Type]="MISCINC" And Nz([quantity],0)<0,"MISCEXP",[Transaction Type])

3. ## Re: FORMULA help (Access2k)

yep, all the quantity has a number (positive or negative) but it doesn't seem to work.
<img src=/S/confused.gif border=0 alt=confused width=15 height=20>

4. ## Re: FORMULA help (Access2k)

When you say it doesn't work, what exactly is happening? Are you getting errors or simply not getting the data you would expect? Can you give an example of how it's going wrong?

5. ## Re: FORMULA help (Access2k)

When the Transaction Type is MISCINC & the quantity is a negative number then it should have MISCEXP in the Transaction Type instead of MISCINC.
But it not doing what its supposed to do.

6. ## Re: FORMULA help (Access2k)

What are you getting instead of MISCEXP? MISCINC or something else?

7. ## Re: FORMULA help (Access2k)

I am importing data from excel. Sometimes MISCINC (MISCelaniousINCome) has negative numbers as quantity and I want those to be saved as MISCEXP (MISCellaniousEXPences).

tt: IIf([Transaction Type]="MiscIncome" And [q]<0,"MiscExpense",IIf([Transaction Type]=" DivIncome" And [q]<0," DivExpense",IIf([Transaction Type]=" IntIncome" And [q]<0,"IntExpense",IIf([Transaction Type]=" MiscAdjInc" And [q]<0," MiscAdjExp",[Transaction Type]))))

8. ## Re: FORMULA help (Access2k)

I know what you're trying to do but what I'm asking is in what way is it not working? I assume you're getting something in the field but it's the wrong thing? If so, what is it that you're getting?
I notice that in all the other iif statements in your expression you've got a space at the beginning of the Type name - e.g. " DivIncome", but you haven't with "MiscIncome" - is that significant?

9. ## Re: FORMULA help (Access2k)

Perhaps I'm just being picky here, but we've found that the IIf doesn't always like compound criteria, so by putting an extra set of ( )'s for the criteria we usually can get it to work:

tt: IIf(([Transaction Type]="MiscIncome" And [quantity]<0),"MiscExpense",IIf(([Transaction Type]=" DivIncome" And [quantity]<0)," DivExpense",IIf(([Transaction Type]=" IntIncome" And [quantity]<0),"IntExpense",IIf(([Transaction Type]=" MiscAdjInc" And [quantity]<0)," MiscAdjExp",[Transaction Type]))))

10. ## Re: FORMULA help (Access2k)

Thank you guys.
Rory you were right, it was the space which was causing the errors. I didn't know space counts.
Anyhow, it works fine now, thanx to you guys.
What'll I do without you guys <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
<img src=/S/bow.gif border=0 alt=bow width=15 height=15>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•