equivalent of IF in Access? Hopefully something v. simple

[vanilladan]

Howdy folks,
Been a while since I used Access and I'm hoping to get to a proficient level. Anyway, humble beginnings...

I have a Table, Orders.
I have another Table, Item Prices.

I have a query which joins the two tables by 'Item No' (a common field) and then shows the 'Item No' & 'Item Details' from the Order Table with the 'Price' from the Item Prices table.
Problem is this:

There are now TWO different 'Price' fields in Item Price table: '2002 Price' and '2003 Price'.

Solution I want!:

I want Access to decide which field to select from based upon the ORDER DATE field in the Orders table.

Any ideas?
A simple Excel equivalent would be:

VLOOKUP(A1,ItemPrices,IF(B1=2002,2,3),FALSE)

presuming column B housed the 'Order Date' entries.

Cheers guys,

Vanilladan

 

[dk]

Hi,

You can use the IIf function which has the following syntax:-

IIF(Expression, Value if True, Value if False)

In your case you could use something like this:-

ItemPrice: IIf(Year([Orders]![Order Date])=2002,[Item Prices]![2002 Price],[Item Prices]![2003 Price])

Hope that helps. Post back if not

 

[vanilladan]

Perfect DK - exactly what I was looking for.
much appreciated.
cheers,

Dan