# Thread: IIF query (Access 2000)

1. ## IIF query (Access 2000)

I want to build an IIF query with 3 additional coloumns. My fields are liters and size. The first column should enumerate the liters where the size is < 6. The second coloumn should enumerathe the liters where the size is between 20 and 60 and in the thrd coloumn where size is > 180. Ii think the best way is an iif query but it is difficult for me to build it. So if for example the product has a size of 1, the liters should be in the irst coloumn.These liters will be further calculated so the query should be updatable.will you help me ?

2. ## Re: IIF query (Access 2000)

What happens if the size is 13, or 100?

3. ## Re: IIF query (Access 2000)

the size of the packages we have are :
small : 0,4,0,5,1, 4,5
garage : 20 60
large : 180 ,205
the liters derived with the packages smaller than 6 are called small, the second garage and the third large

we have no other sizes

4. ## Re: IIF query (Access 2000)

So you meant "greater than or equal to180" instead of "greater than 180". You should always be accurate!

The following query will return all fields from the table, plus three calculated columns L1, L2 and L3. The original fields will be updatable, the additional columns L1 to L3 will *not* be updatable since they are calculated.
<code>
SELECT *, IIf([Size]<6,[Liters],Null) AS L1, IIf([Size] Between 20 And 60,[Liters],Null) AS L2, IIf([Size]>100,[Liters],Null) AS L3
FROM [NameOfTable]
</code>
Replace NameOfTable with the name of your table.

5. ## Re: IIF query (Access 2000)

Thank you very much for your reply.it is beyond me to do that since liters is in one table called order details and size is anohr table called products:
SELECT [order details].liters, products.size
FROM [order details] INNER JOIN products ON [order details].ProductID = products.Productid;
Which name of table should i enter ?

6. ## Re: IIF query (Access 2000)

Try

SELECT [order details].liters, products.size, IIf([Size]<6,[Liters],Null) AS L1, IIf([Size] Between 20 And 60,[Liters],Null) AS L2, IIf([Size]>100,[Liters],Null) AS L3
FROM [order details] INNER JOIN products ON [order details].ProductID = products.Productid;

7. ## Re: IIF query (Access 2000)

it is simply great !

#### Posting Permissions

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