I'm not sure what your calculation would be. One issue I think I see is
returning a string/text value when you should only be returning a number or
null.
However, assuming you want to give a discount of 5% on orders over $1,000.
You might add a column in your query like:
Discount: IIf([SaleAmt] > 1000, [SaleAmt] * 0.05, 0)
I would expect either the threshold amount or the discount percent to change
over time. I would hate to have to remember every where I used this
calculation. So, create a module of business calcs with a function like:
Public Function GetDiscount(curAmount as Currency) as Currency
Dim dblDiscount as Double
Dim dblNoDiscount as Double
Dim curThreshold as Currency
dblDiscount = 0.05
dblNoDiscount = 0.0
curThreshold = 1000
'====== no hard-coded numbers below this line =========
If curAmount > curThreshold Then
GetDiscount = curAmount * dblDiscount
Else
GetDiscount = dblNoDiscount
End If
End Function
You could further refine this by sending in the discount percent as an
optional argument. I would go even further and create a table of amounts
with a related percent. The function could then look up the amount in the
table and grab the appropriate percent. Using a table would actually allow
for elimination of the function altogether but that is another thread ;-)
--
Duane Hookom
Microsoft Access MVP
Hank said:
Thanks Duane,
I am not very experienced in ms access but would like to know more about
this.
I did a google search on "modBusinesCalcs" and guess what -- your name
appears on every hit that I read - so you must be the expert.
Where can I learn more and possibly see some examples of user defined
functions?
To answer your queston, if I have to change from 25 to 30 I would have to
change the code. All for learning a better way.
Thanks again
Hank
Duane Hookom said:
I don't think you should be doing this calculation in an expression in a
query. What happens when you want to change the 25 to 30?
I would create a small user-defined function in a standard module named
"modBusinessCalcs". This provides greater functionality an the ability to
add
comments.
--
Duane Hookom
Microsoft Access MVP
Hank said:
Thanks to both of you. It is working normally now.
Still not able to sove the "None" or Null problem. Actually the #Error
occurs in the sum text box. =Sum([PastDue]) is what I use to get a total
of
the past due payemnts. If I have "None" or " " when there is a zero
amount,
it seems to have a problem adding. In other words if I have a $5.00
past
due from one installment and $0.00 in the next installement it sums to
$5.00. But if I use "None" or " " I get #Erron in the =Sum([PastDue])
text
box.
Hank
I have the following Expression statement in a query:
PastDue:
IIf([DueDate]<Date(),Format(25+Nz([FeeDue],0)-Nz([AmtPaid],0),"Currency"),"$0.00")
The statement run fine and I get what I am asking for.
Now I have tried to change it to read:
PastDue:
IIf([DueDate]<Date(),Format(25,0)-Nz([AmtPaid],0),"Currency"),"$0.00")
This just removes adding a Fee (FeeDue) to any past due amount.
What I am trying to say is -- If today's date is after the due date
(DueDate) then subtract the amount paid (AmtPaid) form $25.00 and
format
as currency and show $0.00 if there is no past due amount.
I keep getting syntax errors. What am I doing wrong?
Question #2: Rather than show $0.00, how can I just leave a blank
space
or say "None" if there is not a past due amount? I have tried
changing
the "$0.00" to "None" but it does not work.
Thanks for all your help
Hank
