IIF in a query (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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 ?

Reply | Quote

>>I do not know how to use switch or choose <>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))

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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 ?

Reply | Quote

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].

Reply | Quote

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 ?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote