I have a report based on a query. The query brings in a price, say $5.00. Then there is a vb case statement behind the print of the report that looks up the price and returns a percent, say in this instance 25%. Then in the code I multiply the total $ of items by the % to get a addon value. I then need to add the addon value to the total items to get the full charge.

Looks like this:
value from query (field on report) for itemA - $100
price from query (field on report) for itemA - $5.00
case statement returns addon percent of 25% - shown in txtfield on report
add on value is $100 * 25% = 25 - shown in txtfield on report
total charge for ItemA $100 + 25 = 125 - shown in txtfield on report

value from query (field on report) for itemB - $200
price from query (field on report) for ItemB - $6.00
case statement returns addon percent of $30%
add on value is $200*30% = 60
total charge for ItemB is $200 + 60 = 260

Grand total of report = 125+260

I get the detail section of the report to work correctly, but I'm unable to get a Grand Total. How do I create a sum of all total charges?

I used the case statement because there are too many values to make a nested If in the query for the addon percent. Right now I'm using whole numbers in the case statement for testing purposes. However, the values need to run like this to run between values. I started with is a base variable of 15% and then will add a half percent as the prices to up like this.

dim base as double
dim price as double

price = me.price
base = 15

Case Price
Case 4
txtaddonpercent = base
Case 5
txt addonpercent = base+ .5
Case 6
txtaddonpercent = base + 1

I need...
Case Price
Case between 4.00 and 4.99
txt... = base
Case between 5.00 and 5.99
txt... = base + .5
Case between 6.00 and 6.99
txt... = base + 1

Doesn't like the code written with between or Case >=4.00 and <5.00. What is the correct syntax?


Thank you!