By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,950 Members | 986 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,950 IT Pros & Developers. It's quick & easy.

multiple markups

P: n/a


-if the Subtotal is equal to or less than 25000, multiply subtotal by
12%, if not set to 0
- If the Subtotal is greater than 25000 but less than or equal to
10000, multiply subtotal by 10%, if not set to 0
- f the Subtotal is greater than 10000, multiply subtotal by 7%, if not
set to 0

Total=Subtotal+(one of the values above)

Can anyone help, i must be writing the expressions incorrectly????

Oct 20 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
dynomite wrote:
>
-if the Subtotal is equal to or less than 25000, multiply subtotal by
12%, if not set to 0
- If the Subtotal is greater than 25000 but less than or equal to
10000, multiply subtotal by 10%, if not set to 0
- f the Subtotal is greater than 10000, multiply subtotal by 7%, if not
set to 0

Total=Subtotal+(one of the values above)

Can anyone help, i must be writing the expressions incorrectly????
I'll assume you meant 100,000, not 10,000.

Public Function GetTotal(SubTotal) As Currency
If Not IsNull(SubTotal) Then
Dim curPercent As Currency
Select Case SubTotal
Case Is <= 25000
curPercent = .12
Case Is <= 100000
curPercent = .1
Case Else
curPercent = .07
End Select
GetTotal = SubTotal * curPercent
Endif
End Function

Total=Subtotal+GetTotal(SubTotal)
Oct 20 '06 #2

P: n/a
you can do something like this:

select sum(amount) As Total, IIf(sum(amount) 100000, sum(amount) *
.07, IIf(sum(amount) >= 25000, sum(amount) * .1, sum(amount) * .12) As
TotalPercent, Total + TotalPercent As TotalAndPecent From table1

The statement uses the immediate If (IIf) statement and nests additional
IIfs so that the statement is really saying this:

If sum(amount) 100000 then
result = sum(amount) * .07
ElseIf sum(amount) >= 25000 Then
result = sum(amount) * .1
Else
result = sum(amount) * .12
End If

So the first field of the query above "Total" displays sum(amount), the
next fields "TotalPercent" displays the assigned percentage of
sum(amount) * ?, and the last field "TotalAndPercent" displays the sum
of Total And TotalPercent. Note: this is the Access version. In sql
server you would use a Case statement.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Oct 20 '06 #3

P: n/a
salad <oi*@vinegar.comwrote in
news:Ms*******************@newsread2.news.pas.eart hlink.net:
Public Function GetTotal(SubTotal) As Currency
If Not IsNull(SubTotal) Then
Dim curPercent As Currency
Select Case SubTotal
Case Is <= 25000
curPercent = .12
Case Is <= 100000
curPercent = .1
Case Else
curPercent = .07
End Select
GetTotal = SubTotal * curPercent
Endif
End Function
Naturally, for your function declaration you meant:

Public Function GetTotal(SubTotal As Variant) As Currency

because without it, it's completely invalid (parameter types have to
be declared).

I also never declare variables in the middle of code. I used to, on
the theory that if you don't need a variable, declaring it only when
you need it would use less memory. But the memory involved would be
so tiny, seems to me, that it's not justified given the problems it
causes in searching for the variable declarations for someone
reading the code later.

Also, your terminology is confusing. What you're calculating is the
amount to be added to the subtotal, as the OP asked:
Total=Subtotal+(one of the values above)
You're providing the value indicated by "(one of the values above)",
so calling it "GetTotal" is misleading, as what you're getting is
the percent of markup or commission or tax or whatever.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 20 '06 #4

P: n/a
salad <oi*@vinegar.comwrote in
news:Ms*******************@newsread2.news.pas.eart hlink.net:
Public Function GetTotal(SubTotal) As Currency
If Not IsNull(SubTotal) Then
Dim curPercent As Currency
Select Case SubTotal
Case Is <= 25000
curPercent = .12
Case Is <= 100000
curPercent = .1
Case Else
curPercent = .07
End Select
GetTotal = SubTotal * curPercent
Endif
End Function
In a query you could do this without a function with this:

SubTotal + (SubTotal *
IIf(SubTotal<=25000,.12,IIf(SubTotal<=100000,.1,.0 7)))

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 20 '06 #5

P: n/a
David W. Fenton wrote:
salad <oi*@vinegar.comwrote in
news:Ms*******************@newsread2.news.pas.eart hlink.net:

>>Public Function GetTotal(SubTotal) As Currency
If Not IsNull(SubTotal) Then
Dim curPercent As Currency
Select Case SubTotal
Case Is <= 25000
curPercent = .12
Case Is <= 100000
curPercent = .1
Case Else
curPercent = .07
End Select
GetTotal = SubTotal * curPercent
Endif
End Function


Naturally, for your function declaration you meant:

Public Function GetTotal(SubTotal As Variant) As Currency

because without it, it's completely invalid (parameter types have to
be declared).
Really? I was under the impression if you don't declare the type, it is
a variant. Ex:
Public Function Sum2Vars(var1, var2) As Integer
Sum2Vars = var1 + var2
End Function
? Sum2Vars(1,2)
3

I always explicitly define the type in my apps, but I wasn't aware it
was mandatory.
>
I also never declare variables in the middle of code. I used to, on
the theory that if you don't need a variable, declaring it only when
you need it would use less memory. But the memory involved would be
so tiny, seems to me, that it's not justified given the problems it
causes in searching for the variable declarations for someone
reading the code later.
For the most part, neither do I. In this case, I view it as an example
that works and won't get perturbed by putting a Dim inside the IF.

Sometimes I will read something someone is doing that I consider a bit
radical...not a normal way of doing things...but I will rarely question
why they are going down that path...I'll simply pass a response back
that hopefully works for the person. I suppose I could berate the
person for their stupidity for doing it a particular way...but I'm not
that judgemental. As a manager once said, "An ugly program that works
is preferable to an pretty one that doesn't."
>
Also, your terminology is confusing. What you're calculating is the
amount to be added to the subtotal, as the OP asked:

>>Total=Subtotal+(one of the values above)


You're providing the value indicated by "(one of the values above)",
so calling it "GetTotal" is misleading, as what you're getting is
the percent of markup or commission or tax or whatever.
You are correct. Next time I write this function for someone on this
newsgroup I will call it AddToTotal.
Oct 20 '06 #6

P: n/a
salad <oi*@vinegar.comwrote in
news:fp******************@newsread4.news.pas.earth link.net:
David W. Fenton wrote:
>salad <oi*@vinegar.comwrote in
news:Ms*******************@newsread2.news.pas.ear thlink.net:

>>>Public Function GetTotal(SubTotal) As Currency
If Not IsNull(SubTotal) Then
Dim curPercent As Currency
Select Case SubTotal
Case Is <= 25000
curPercent = .12
Case Is <= 100000
curPercent = .1
Case Else
curPercent = .07
End Select
GetTotal = SubTotal * curPercent
Endif
End Function

Naturally, for your function declaration you meant:

Public Function GetTotal(SubTotal As Variant) As Currency

because without it, it's completely invalid (parameter types have
to be declared).

Really? I was under the impression if you don't declare the type,
it is a variant. Ex:
Public Function Sum2Vars(var1, var2) As Integer
Sum2Vars = var1 + var2
End Function
? Sum2Vars(1,2)
3

I always explicitly define the type in my apps, but I wasn't aware
it was mandatory.
Well, just try it. While one can declare variables implicitly, as
you say, in function/subroutine declarations, it won't compile. It
gives an error as soon as you leave the declaration line (i.e., the
precompile check for validity).
>I also never declare variables in the middle of code. I used to,
on the theory that if you don't need a variable, declaring it
only when you need it would use less memory. But the memory
involved would be so tiny, seems to me, that it's not justified
given the problems it causes in searching for the variable
declarations for someone reading the code later.

For the most part, neither do I. In this case, I view it as an
example that works and won't get perturbed by putting a Dim inside
the IF.

Sometimes I will read something someone is doing that I consider a
bit radical...not a normal way of doing things...but I will rarely
question why they are going down that path...I'll simply pass a
response back that hopefully works for the person. I suppose I
could berate the person for their stupidity for doing it a
particular way...but I'm not that judgemental. As a manager once
said, "An ugly program that works is preferable to an pretty one
that doesn't."
But a pretty program that also works is better than either of those.

My concern here is maintainability of the code. The code will be
read many more times than it will be written, and shortcuts in the
coding process itselve often make maintenance of that code much
harder.

I try to code so that another programmer coming to the code sometime
in the future will be able to understand the code at a glance, with
a minimum of poking around to figure out where the variables are
declared. One thing I've started doing more is not using
module-level variables for things being looked up and used in
functions. Instead I use Static variables within the functions. If
the value of the variable has to be assigned, I'll use a
module-level private variable and a property Let/Get. The whole
reason for this is to cut down on the use of global and public
module-level variables so that the values can be much more easily
controlled.

Sure, not doing it that way can work, but having these strict
restrictions produces code that is less prone to being accidentally
misused, and is thus easier to maintain in the long run.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 21 '06 #7

P: n/a
David W. Fenton wrote:
salad <oi*@vinegar.comwrote in
news:fp******************@newsread4.news.pas.earth link.net:

>>David W. Fenton wrote:
>>>salad <oi*@vinegar.comwrote in
news:Ms*******************@newsread2.news.pas.e arthlink.net:

Public Function GetTotal(SubTotal) As Currency
If Not IsNull(SubTotal) Then
Dim curPercent As Currency
Select Case SubTotal
Case Is <= 25000
curPercent = .12
Case Is <= 100000
curPercent = .1
Case Else
curPercent = .07
End Select
GetTotal = SubTotal * curPercent
Endif
End Function

Naturally, for your function declaration you meant:

Public Function GetTotal(SubTotal As Variant) As Currency

because without it, it's completely invalid (parameter types have
to be declared).

Really? I was under the impression if you don't declare the type,
it is a variant. Ex:
Public Function Sum2Vars(var1, var2) As Integer
Sum2Vars = var1 + var2
End Function
? Sum2Vars(1,2)
3

I always explicitly define the type in my apps, but I wasn't aware
it was mandatory.

Well, just try it. While one can declare variables implicitly, as
you say, in function/subroutine declarations, it won't compile. It
gives an error as soon as you leave the declaration line (i.e., the
precompile check for validity).
I did compile and save the above Sum2Vars function....A97 Works fine.
Does it not compile in later versions?
>>>I also never declare variables in the middle of code. I used to,
on the theory that if you don't need a variable, declaring it
only when you need it would use less memory. But the memory
involved would be so tiny, seems to me, that it's not justified
given the problems it causes in searching for the variable
declarations for someone reading the code later.

For the most part, neither do I. In this case, I view it as an
example that works and won't get perturbed by putting a Dim inside
the IF.

Sometimes I will read something someone is doing that I consider a
bit radical...not a normal way of doing things...but I will rarely
question why they are going down that path...I'll simply pass a
response back that hopefully works for the person. I suppose I
could berate the person for their stupidity for doing it a
particular way...but I'm not that judgemental. As a manager once
said, "An ugly program that works is preferable to an pretty one
that doesn't."

But a pretty program that also works is better than either of those.

My concern here is maintainability of the code. The code will be
read many more times than it will be written, and shortcuts in the
coding process itselve often make maintenance of that code much
harder.

I try to code so that another programmer coming to the code sometime
in the future will be able to understand the code at a glance, with
a minimum of poking around to figure out where the variables are
declared. One thing I've started doing more is not using
module-level variables for things being looked up and used in
functions. Instead I use Static variables within the functions. If
the value of the variable has to be assigned, I'll use a
module-level private variable and a property Let/Get. The whole
reason for this is to cut down on the use of global and public
module-level variables so that the values can be much more easily
controlled.

Sure, not doing it that way can work, but having these strict
restrictions produces code that is less prone to being accidentally
misused, and is thus easier to maintain in the long run.
Interesting. About the only place I use Global variables, application
wide, is in WinAPI calls...like you see at mvps.org and sometimes in
Form modules. In forms, I may also create a control to stuff data and
make them invisible.
Oct 21 '06 #8

P: n/a
salad <oi*@vinegar.comwrote in
news:GU******************@newsread1.news.pas.earth link.net:
David W. Fenton wrote:
>salad <oi*@vinegar.comwrote in
news:fp******************@newsread4.news.pas.eart hlink.net:

>>>David W. Fenton wrote:

salad <oi*@vinegar.comwrote in
news:Ms*******************@newsread2.news.pas. earthlink.net:

>Public Function GetTotal(SubTotal) As Currency
If Not IsNull(SubTotal) Then
Dim curPercent As Currency
Select Case SubTotal
Case Is <= 25000
curPercent = .12
Case Is <= 100000
curPercent = .1
Case Else
curPercent = .07
End Select
GetTotal = SubTotal * curPercent
Endif
>End Function

Naturally, for your function declaration you meant:

Public Function GetTotal(SubTotal As Variant) As Currency

because without it, it's completely invalid (parameter types
have to be declared).

Really? I was under the impression if you don't declare the
type, it is a variant. Ex:
Public Function Sum2Vars(var1, var2) As Integer
Sum2Vars = var1 + var2
End Function
? Sum2Vars(1,2)
3

I always explicitly define the type in my apps, but I wasn't
aware it was mandatory.

Well, just try it. While one can declare variables implicitly, as
you say, in function/subroutine declarations, it won't compile.
It gives an error as soon as you leave the declaration line
(i.e., the precompile check for validity).

I did compile and save the above Sum2Vars function....A97 Works
fine. Does it not compile in later versions?
Well, what do you know. It compiles now. When I cut and pasted it
before it wouldn't compile, and I have no idea why.

In any event, *not* declaring parameter types is terrible
programming practice even when the implicit typing is the same as
the explicit (i.e., it will be a variant).

I thought I'd just cut and pasted, but apparently I was typing and
not typing the same thing. I did it twice, once in response to the
first post, the second time in response to the above post.

I wonder what I was doing wrong? :)
>>>>I also never declare variables in the middle of code. I used to,
on the theory that if you don't need a variable, declaring it
only when you need it would use less memory. But the memory
involved would be so tiny, seems to me, that it's not justified
given the problems it causes in searching for the variable
declarations for someone reading the code later.

For the most part, neither do I. In this case, I view it as an
example that works and won't get perturbed by putting a Dim
inside the IF.

Sometimes I will read something someone is doing that I consider
a bit radical...not a normal way of doing things...but I will
rarely question why they are going down that path...I'll simply
pass a response back that hopefully works for the person. I
suppose I could berate the person for their stupidity for doing
it a particular way...but I'm not that judgemental. As a manager
once said, "An ugly program that works is preferable to an pretty
one that doesn't."

But a pretty program that also works is better than either of
those.

My concern here is maintainability of the code. The code will be
read many more times than it will be written, and shortcuts in
the coding process itselve often make maintenance of that code
much harder.

I try to code so that another programmer coming to the code
sometime in the future will be able to understand the code at a
glance, with a minimum of poking around to figure out where the
variables are declared. One thing I've started doing more is not
using module-level variables for things being looked up and used
in functions. Instead I use Static variables within the
functions. If the value of the variable has to be assigned, I'll
use a module-level private variable and a property Let/Get. The
whole reason for this is to cut down on the use of global and
public module-level variables so that the values can be much more
easily controlled.

Sure, not doing it that way can work, but having these strict
restrictions produces code that is less prone to being
accidentally misused, and is thus easier to maintain in the long
run.
Interesting. About the only place I use Global variables,
application wide, is in WinAPI calls...like you see at mvps.org
and sometimes in Form modules. In forms, I may also create a
control to stuff data and make them invisible.
Public/global constants are fine. It's variables whose values can be
changed that don't belong at the global level.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Oct 21 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.