40 IIf statements efficiently??

[abrown]

I have a table with item # and retail price. The retail prices range from $0.00 to $400.00

I would like to create a third column called Retail range. Retail range is in increments of $5. For example:

0-4.99
5-9.99
10-14.99
15-19.99
etc all the way to 400

Can someone suggest the best way to accomplish this task??? IIf's, queries, combo boxes???

 

[PaulF]

I can't find any documentation on the partition function (one wonders how many other undocumented but useful functions exist) but I tried adding decimals and it would not accept them.

One ugly solution is to multiply the relevant pieces in the function by 100 and deal with cleaning up the formatting in the next steps.

Paul

 

[bat17]

You could always use a lookup table and use the dlookup function to find you value. This will have the flexibility of alowing any set of values.

Peter

 

[neilshah]

Thanks for all the great advice. The 'Upper Limit' & the 'Partition' formulas work great. However, the ranges we are trying to create are not constant i.e. we have ranges of 5 and ranges of 10. Hence we had to use the 'Upper Limit' function as opposed to Partition function.

Now to give a price range to each price, I created another table and manually put in the 'Price Range' for each upper limit (shown below) and linked the tables.

Upper Limit Price Range
5 $0 to $5
10 $5 to $10
15 $10 to $15
20 $15 to $20
25 $20 to $30
30 $20 to $30
35 $30 to $40
40 $30 to $40
45 $40 to $50
50 $40 to $50
...
400 $390 to $400

However, in the price column of the original table there are some text values and so I am getting #Error in the 'Price Range' column. When I run the query I get this error message - 'Data Mismatch exception'.

Any suggestions on how to get rid of the error? What I would ideally like is that if there is text in the price column, 'No Price Given' should appear in the 'Price Range column'.

Thanks..

Neil

 

[bat17]

You could use the IsNumeric function combined with an IIF statement to return -10 if the price coulumn value is not numeric and then add a -10 = 'No Price Given' to your look up table

HTH

peter