Build an Expression or time for VBA?

[streub]

I have a query with an expression to calculate selling price based upon age.



Discounted Price: [code]IIf([AGE]<31,[CST]/0.6,IIf([AGE]<61,[CST]/0.7,IIf([AGE]<91,[CST]/0.8,IIf([AGE]<121,[CST]/0.9,[CST]/1))))[code]

This expression executes perfectly; however, there are now items that are exempt from this pricing strategy. There are brands that must be priced based upon a predetermined retail price. Additionally, there are items based upon their product type that I need to use the same pricing as for brands.

Brand "Tulo" has a fixed selling price that cannot be discounted.

Item "box spring" or "foundation" has a fixed price that cannot be discounted.

I believe it now time to use VBA for pricing but unsure which method is best. Do I used "If . . .then else" or "Select Case"?

[davegri]

Queries can call upon functions to calculate a value.
If you use a UDF (user defined function), it can contain any VBA code that you wish.
I'm partial to Select Case...

[Micron]

This may not need vba at all. If your tables were structured so that they contained fields to support this, your query could apply the discount where the record contained a discount rate. So a discount Yes/No field is probably not needed either since the absence of a rate means it cannot be discounted. Not saying you shouldn't use vba since it is more powerful much of the time. Just saying long IIF expressions are not only unwieldy, they're not very flexible when things change, and you should have options.

[streub]

Are you stating the discount rate should be a field in the data entry form?

[Micron]

Are you stating the discount rate should be a field in the data entry form?

If applicable to the task at hand. What I'm really saying is that putting discount rates into a table is an option. How you do that and how you make use of the data (form field or not) depends on your process and database. If your products table had a discount field, you could use that, but it would only be updatable and not easily archived, and the product ID/discount would have to be singular since you wouldn't repeat all the product field data for each possible discount. If multiple discount possibilities exist for product, a discount table may be required, where the product ID could be repeated many times for each discount possibility. If on a form, I suspect you ought to either hide or disable the control unless you want to give the ability to edit the discount amount/rate. Of course, some discounts are given purely on a quantity basis, often with a maximum discount amount. In those cases, it's simpler than what's described above. A simple form control expression could suffice (in an unbound field of course).

[ssanfu]

Consider:
have a field "Discountable" (type Boolean) in the product table.
have a table with fields Age & Discount rate.
write a UDF with parameters to calculate the discount.

Easy to change/update Age/discount rate and which products can have discounts.


UDF would get & return the discount rate based on age and if product discount available.
Or UDF could be passed the Age, Discountable, and amount (CST), to calculate the amount.

[streub]

Excellent suggestions!

Thank you

[ArviLaanemets]

In case most for of items the discount is calculated by standard formula, you can consider to have a table tblDiscountExceptions: DEID, Brand, Age, Discount;

In this table, you can determine brands which don't have discount at all by a single row with Age and Discount empty (Null). And you can determine discounts not mathing the general formula entering according rows with age, and matching discount.

Now when the brand exist in discount exceptions table, you calculate matching discount from exceptions table using Nz(Discount,1) or Nz(Discount,0) depending the formula you use, otherwise you apply the standard formula.

[streub]

Sorry for not responding sooner. I was testing events and macros (using Macro Builder) but have not attempted to use VBA.

The issue I am experiencing with the macro builder is it does not recognize queries and their fields.

Example:

# If [Inventory Sort by Name and Size]![PRT] = "box spring" or "foundation"
Then [Reports]![Outlet Price Tag]![Discounted Price] = [Inventory Sort by Name and Size]![OOR]
End if #

[Micron]

You aren't referencing them correctly. First, you have to be more specific and not write the way you interpret but more like a computer interprets.
I get your drift, but if I said to you

"If ball is red" or.
"Black".

It should be thought of as
"If
ball is red
or
ball is black". Get it? That may not be the best example, but it's all I could come up with at the moment. If you built a query in design view with the color field having "black" on one row then "blue" on the next row (same field) then switched to sql view, you could see it for yourself.

Second, you cannot simply use the name of the form control (or the field it's linked to) in vba or query sql. That would be like me saying to you that I live at 123 Main street without telling you what state/province or city I live in. If the control is on a main form, the syntax is Forms!NameOfYourMainForm.NameOfYourControl. If it's a subform, then that's not good enough either. Note: I don't allow form/report controls to have the same name as their linked fields. This is what the Access wizards will give you, but I once discovered that the code couldn't differentiate between the field or control since they both had the same name. If I use a wizard, I go through the controls and give them properly constructed names. My own practice deviates a bit from this link, but see this for example: http://access.mvps.org/access/general/gen0012.htm

I'm guessing the #'s are your way of delimiting code or sql instead of using the forum code tag button on the post toolbar. Hope the above helps.

[ssanfu]

One more thing.

You should NOT have SPACES in object names.

[Inventory Sort by Name and Size] form name has spaces
[Outlet Price Tag]![Discounted Price] form name and control name have spaces

Better:
[InventorySortByNameAndSize] or
[Inventory_Sort_by_Name_and_Size] (name is getting really long - lots of typing )
[InvSortNameSize]

[OutletPriceTag]![DiscountedPrice] or
[Outlet_Price_Tag]![Discounted_Price]

[Micron]

Thanks for the reminder, and pitching in Steve.
Actually, if no spaces, no need for the [brackets] either, right? Not that that's all inclusive, but the other reasons for needing them aren't in play here.

[streub]

Well, I got the spaces removed from the object names