424,950 Members | 986 Online
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
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