Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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])
    Francois

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: FORMULA help (Access2k)

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

    Microsoft MVP - Excel

  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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?
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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]))))
    Carpy Diem, it&#39;s .

  10. #10
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •