Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IIF in a query (Access 2000)

    i want to build a calculted field in my query depending on the number of the field afid. For example if Afid = 5 then the calculated field should be 4000000 + [paymentid)
    If afid = 2, the calculated field should be 2000000 + [paymentid]
    etc etc
    How can i build the iif condition in the calculated field ? I am applying the attachement
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: IIF in a query (Access 2000)

    I think you may want to use either the Choose() or Switch() functions for this (depending on the exact situation) rather than IIF. While you could use IIF, nested IIF statements are very hard to read, and easy to mess up.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF in a query (Access 2000)

    I do not know how to use switch or choose. I know only the IIfcondition. However it gives me an error when i place the condition in the calculated field :
    IIF([afid]= 2,2000000+[paymentid],IIF([afid] = 3,5000000+paymentid))
    The eror shows that i am missing a bracket.
    Could you help me on the basis of my attachement ?

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: IIF in a query (Access 2000)

    >>I do not know how to use switch or choose <<

    That's why there is Help.


    >>IIF([afid]= 2,2000000+[paymentid],IIF([afid] = 3,5000000+paymentid))<<

    Like I said, nested IIF statements are hard to read. Try this:

    IIF( [afid] = 2, 2000000 + [PaymentID], IIF( [afid] = 3, 5000000 + [PaymentID], NULL))
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIF in a query (Access 2000)

    Instead of using a complicated expression, I would add a field Increment to the Affiliates table with the required increment. For afid=5, Increment would be 4000000 etc.
    In the query, you can simply use [Increment]+[PaymentID]

    Note: you should join Affiliates to Customers on afid in the query.

  6. #6
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF in a query (Access 2000)

    Of course,it is as simple as that ! It is so easy now! Thank you so much ! I cannot understand how i couldnt find this solution myself ! I was thinking in the wrong direction all the time

  7. #7
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF in a query (Access 2000)

    Dear Hans

    my query shows the error ambigiou outer joins.As you have told me i have to pay attention to the join between affiiates and customers,I tried the three possiilities for the join but unsucessfully.Would ou have a look at my query ?
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIF in a query (Access 2000)

    Set *all* the joins in the query to option 1.

    If you really need the Order Details table in the query, you will have to specify which PaymentID field you want to use in [Increment]+[PaymentID] since both the Orders table and the Order Details table have a field named PaymentID. Depending on what you want, use either [Increment]+[Orders].[PaymentID] or [Increment]+[Order Details].[PaymentID].

  9. #9
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF in a query (Access 2000)

    I have set all the joins in the query to option 1.Also i have deleted the field paymentid in the table orderdetails since i do not need it.But again i get the error "ambigious outer joins". Could you help ?
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIF in a query (Access 2000)

    Look at the line joining Customers and Orders. There is an arrow pointing to Customers. This indicates that you have NOT set this join to option 1. It is still set to option 3.

  11. #11
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF in a query (Access 2000)

    Thank you for everything.Just to let you now my query is OK now

Posting Permissions

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