Access IIF query - using date ranges

Question

I am trying to create an iif statement that uses date ranges to determine the amount of revenue that I am generating from the up sell of a specific component.  I need to use the different date ranges because of annual (sometimes bi-annual) price increases.

I'm using the discount of the overall product and trying to apply it to the specific component price depending on when the order was booked.  The below query works but its only using (1-[DISCOUNT])*160 no matter what the date is.

(1-[DISCOUNT])*IIf([BOOKED_DATE]<7/1/2008,100,IIf([BOOKED_DATE] Between 7/1/2008 And 12/31/2008,110,IIf([BOOKED_DATE] Between 1/1/2009 And 12/31/2009,120,IIf([BOOKED_DATE] Between 1/1/2010 And 12/31/2010,130,IIf([BOOKED_DATE] Between 1/1/2011 And 9/31/2011,140,IIf([BOOKED_DATE] Between 10/1/2011 And 9/31/2012,150,IIf([BOOKED_DATE]>10/1/2012,160,""))))))) AS [Component SALES_$]

Answer 1

What are you really trying to do as I have always had discounts applied at the time of sell/purchase and not at some later date.

A PRICING table would have an autonumber primary key, ProductID (from Products table), EffDate, Price, EndDate.  The price record would be valid so long as the EndDate was not completed.

Then you would have a separate table for Sales and include Price, Discount, SalePrice, Tax, and Freight.

Answer 2

I think the should be: the ComponentPrice table with three fields:

EffectiveDate      DateTime

ComponentID    ForeignKey to components table

Price                   Currency

The table's primary key can be a compound key woth the two fields EffectiveDate and ComponentID  or it can be an additional field (autonumber?)

Then you can find the price for any component at any date with this kind of subquery:

(1-[Discount])*(SELECT TOP 1 Price FROM ComponentPrice WHERE ComponentPrice.EffectiveDate < BookedDate AND ComponentPrice.ComponentID = <outer Query's table name>.ComponentID ORDER BY  ComponentPrice.EffectiveDate DESC)

Answer 3

I tried attaching picture but it didn't go through...  Here's what it looked like

Effective_Date              Component_1

1/1/2008                     100

7/1/2008                     110

1/1/2009                     120

etc                              etc.

Answer 4

Ok, I'd like to take the more efficient route as I will use it more often.  Would I create a table like this?  Or would it look like something else?  I named the table PRICING.

How would the code work?

Answer 5

The quick answer is that any literal date must be delimited by #:

IIF(]Booked_Date[ < #7/1/2008#, ...

What's happening now is that it's treating the expression 7/1/2008 as a division, giving an answer around 0.0035, which corresponds to about five minutes after midnight on December 30, 1899 when converted to a date.

The better answer is... don't use the inefficient, hard to maintain, complex IIF logic AT ALL! Instead, use a table driven solution: there are several ways to do it, but one would be to have a rate table with two fields - EffectiveDate and Rate. You could use a "non equi join" subquery:

(1-[Discount])*(SELECT Rate FROM RateTable WHERE RateTable.EffectiveDate = (SELECT Max(X.[EffectiveDate] FROM RateTable AS X WHERE X.EffectiveDate < [BookedDate]))

This would let you maintain the rate increases by simply adding or editing a record in a table, rather than digging into a complex nested IIF statement.