IIf function for 2 parameters

[nadsweb]

Hi
I would really appreciate some help. I'm not very good at Access and right now I have to make an Database for part of an Internship project. So this Q might seem silly to you but I really don't know hoz to proceed!

I've got a table with products and their prices, however the prices changed recently and from one exact date to another they should show this on my table.
The product numbers haven't changed however. If there some function like the IIf function which supports 2 parameters?

Thanks in advance!!!

[ilearneditonline]

Hi
I would really appreciate some help. I'm not very good at Access and right now I have to make an Database for part of an Internship project. So this Q might seem silly to you but I really don't know hoz to proceed!

I've got a table with products and their prices, however the prices changed recently and from one exact date to another they should show this on my table.
The product numbers haven't changed however. If there some function like the IIf function which supports 2 parameters?

Thanks in advance!!!

So is this a MySQL question or an Access question since you said you are not good at access.

If it is MySql you can use

IF(condition, true, false) 

.See mysql documentation.

If it is Access then you use

IIF(condition, true, false)

. See Access Online.

[r035198x]

So is this a MySQL question or an Access question since you said you are not good at access.

If it is MySql you can use

IF(condition, true, false) 

.See mysql documentation.

If it is Access then you use

IIF(condition, true, false)

. See Access Online.

And now this lies in the Access forum

[nadsweb]

Hi
Yes but I can only unsthe IIF Function for one parameter. The thing is, i need something which says: iif delivery date>31.06.07 and productnumber is 789456-123, then 400$, if delivery date <31.06.07 and productnumber 789456-123 then 450$.

Thanks already for your help!

[ilearneditonline]

Hi
Yes but I can only unsthe IIF Function for one parameter. The thing is, i need something which says: iif delivery date>31.06.07 and productnumber is 789456-123, then 400$, if delivery date <31.06.07 and productnumber 789456-123 then 450$.

Thanks already for your help!

So why not use

 
if (deliverydate >) 31.06.07 and (productnumber = 789456-123) then
    retval = 400 
elseif (deliverydate < 31.06.07) and (productnumber = 789456-123) then
    retval = 450
end if
 

[JKing]

Hi
Yes but I can only unsthe IIF Function for one parameter. The thing is, i need something which says: iif delivery date>31.06.07 and productnumber is 789456-123, then 400$, if delivery date <31.06.07 and productnumber 789456-123 then 450$.

Thanks already for your help!

You could use nested iifs.

iif([Delivery Date] > #31/06/07#, iif([ProductNumber] = 789456-123, 400$, 0$),iif([ProductNumber] = 789456-123, 450$, 0$))
 

[nadsweb]

You could use nested iifs.

iif([Delivery Date] > #31/06/07#, iif([ProductNumber] = 789456-123, 400$, 0$),iif([ProductNumber] = 789456-123, 450$, 0$))
 

Hi
The thing is, all my products have new prices. is there a possiblity of saying iif (d.d > 31.06.07, "new_price", "old price") if i have the old and new prices in a related table?

Thanks!

[Killer42]

The thing is, all my products have new prices. is there a possiblity of saying iif (d.d > 31.06.07, "new_price", "old price") if i have the old and new prices in a related table?

Well, I suppose the performance might be abysmal, but you could create a function in VBA which looks up the appropriate price, then just call that function in your query.
So instead of building an elaborate IIF() setup, you'd just use something like PriceAsAtDate(DateField, ProductField).

Depending on the size and volatility of the data, it might be worth caching in an array or something, to speed up these lookups.

[JKing]

Yes, you can have it return the values of another table. You'd simply have to switch the values with the field names of the new and old prices. If your tables are properly related everything should match up just fine.

[nadsweb]

Yes, you can have it return the values of another table. You'd simply have to switch the values with the field names of the new and old prices. If your tables are properly related everything should match up just fine.

Yes but the thing is I still need the old prices. I have to give out warranty claims and for older orders the warranty claim should match up with the old price.

[Killer42]

Yes but the thing is I still need the old prices. I have to give out warranty claims and for older orders the warranty claim should match up with the old price.

It seems as though it should be possible to use some sort of aggregate function or something, to retrieve the first record which matches one field and is equal to or less than another field.

Bear with me, I'm just thinking out loud, here...

If your lookup table had a field which had the product ID (forget what it's called) and the date combined, then all you'd need do is find the first record in sequence, starting from Product&Date, sorted descending.

I'm going to see whether I can get some real Access gurus to pop in, as I'm getting out of my depth.

[NeoPa]

Killer's on the right lines here.
You need to separate out the products from the prices. Prices need to be dated so something like :
Table Name=tblProduct

Field; Type; IndexInfo
ProdID; Autonumber; PK
ProdDesc; String
... Other product specific info

Table Name=tblProdPrice

Field; Type; IndexInfo
ProdPriceID; Autonumber; PK
ProdID; number; CFK (part of compound key)
PriceDate; Date/Time; CFK
Price; Numeric

Link in the two tables on [ProdID] and select only the dates you want in your WHERE clause.

[Killer42]

And presumably you can use a <= operator in the WHERE clause. Requiring an exact match wouldn't be much help.

But, how would you ensure that you got the latest as at that date? In other words, let's say the price has changed five times. For a given date, you would need the last price set on or before that date, but not the earlier ones. How would you specify, in SQL, that you want the first (or last) value in the key range? This is what stumped me.

[NeoPa]

Use the WHERE clause to exclude any after the date you want then use the Max() aggregate function to select the most appropriate record from those that are left.

[Killer42]

Use the WHERE clause to exclude any after the date you want then use the Max() aggregate function to select the most appropriate record from those that are left.

Ahah! Max() is what I was thinking of. I knew there was something that would do it. Thanks, NeoPa.

[NeoPa]

No probs Killer :)
I should have mentioned (for Joe Public) that the GROUP BY clause is also required, but I'm sure you don't need telling that.