Need help on Logic & Code for Grid

[markey]

Hello all, I'm stumped on this one. Hoping someone can provide logic and/or code for the attatched grid. I have two text boxes (txtWeeklyPts1 & txtPotentialPayout1) on a form in Access 2003..... all unbound.

fyi: The entire calculator is coded in VB

This is how the calculator should work:
ie
Person inputs 150 into (txtWeeklyPts1). Now the tiers should work by taking 100pts at $1.00 and the remaining 50pts $1.15, summing both of the tiers up for a total of $157.50, which should appear in (txtPotentialPayout1). The attatched makes this easier to understand.

Any assistance is much needed. I've tried everything to no avail. This is the last part of my project, and it unfortunately has caused the most issues for me. Thanks in advance!

 

[markey]

I'm not sure if an "IF or WITH" statement would be best for this situation. If so, can someone get me started on the right foot please. Here is code I used for a similar scenario:
With Me.txtA8bonus
.Value = IIf(Me.txtA8 <= 69.99, 0, _
IIf(Me.txtA8 <= 79.99, 20, _
IIf(Me.txtA8 <= 84.99, 35, _
IIf(Me.txtA8 <= 94.99, 85, _
IIf(Me.txtA8 <= 104.99, 130, _
IIf(Me.txtA8 > 105, 170, 170))))))
End With

 

[ajetrumpet]

With Me.txtA8bonus
.Value = IIf(Me.txtA8 <= 69.99, 0, _
IIf(Me.txtA8 <= 79.99, 20, _
IIf(Me.txtA8 <= 84.99, 35, _
IIf(Me.txtA8 <= 94.99, 85, _
IIf(Me.txtA8 <= 104.99, 130, _
IIf(Me.txtA8 > 105, 170, 170))))))
End With

Is that the coding you got here?

As far as the question is concerned, these are all textboxes in the attachment, right? How did you get the value in the bottom textbox? Did you concat strings and a function to arrive at that? I say that you will have to define what range the user entered value is in first (with an IIF, or nested IIF, or maybe even a simple nested IF, THEN), and then cancat that string value by way of the decimal, so you can perform mathematical operations on each portion of it. Regardless, if these are all unbound boxes and someone is going to be entering values into a another box and getting the result to pop up for them, it will be a tad bit complicated.

No matter what way you do it though, the fact remains that you will have to extract the portions of the value on both sides of the decimal to apply the appropiate formulas. I can see using the Instr and Len funcions for this. Does this get you going at least?

 

[markey]

Reply

Is that the coding you got here?

As far as the question is concerned, these are all textboxes in the attachment, right? How did you get the value in the bottom textbox? Did you concat strings and a function to arrive at that? I say that you will have to define what range the user entered value is in first (with an IIF, or nested IIF, or maybe even a simple nested IF, THEN), and then cancat that string value by way of the decimal, so you can perform mathematical operations on each portion of it. Regardless, if these are all unbound boxes and someone is going to be entering values into a another box and getting the result to pop up for them, it will be a tad bit complicated.

No matter what way you do it though, the fact remains that you will have to extract the portions of the value on both sides of the decimal to apply the appropiate formulas. I can see using the Instr and Len funcions for this. Does this get you going at least?

Sorry, the code provided above was just an example and not related to this project. For this project, the attatchment was for illustration purposes as to how the "Potential Payout" is calculated. I've attatched a new illustration. The user enter a value in txtWeeklyPts1 and based on the number everything is calculated (based off the grid structure) and displayed in txtPotentialPayout1. I hope this new attatchment gives a better picture. In regards to the Instr and Len functions, I'm not familiar with these. I'll look them up but any direction on code or logic is much appreciated. Thanks for your response

 

[ajetrumpet]

fyi: The entire calculator is coded in VB

What do you mean the entire calculator in VB? You mean you want to perform the calculations entirely in an event?

Is what you call "the grid", actually a table? If it is, I hope you don't have values stored in there like I see with the attachment.

Assuming you are just using pre-assigned values for calculations, the following code will perform the appropriate calculation with the click of a button...

Button_Click

dim x as long
  dim p as long
    dim a as currency
    dim b as currency
  dim c as currency
dim d as currency

a = 1.00
  b = 1.15
    c = 1.30
    d = 1.45
  p = me.points
x = 100

SELECT Case Me.Points

  Case is > 0 and <= 25
    me.payout = 0

  Case is > 25 and <=100
    me.payout = p * a

  Case is > 100 and <=200
    me.payout = (x * a) + ((p - x) * b)

  Case is > 200 and <= 300
    me.payout = (x * a) + (x * b) + ((p - (2 * x)) * c)

  Case is > 300
    me.payout = (x * a) + (x * b) + ((p - (3 * x)) * d)

End Select

This doesn't look elegant does it? That's because it's not! Let me know what you think of it.

That whole thing is assuming you don't have your grid data in a table somewhere.

 

[markey]

This is starting to make sense, but now I'm getting another problem

Thank you for the response. It looks like this is going to work, but I get the following error ("Compile Error: Expected Expression"). If I remove the "and" in the Case Statements, it gets rid of the red, but I still get the same error. Any idea on this one? Thanks again

Here is the code I'm using:

Private Sub txtWeeklyPts1_LostFocus()
Dim x As Long
Dim p As Long
Dim a As Currency
Dim b As Currency
Dim c As Currency
Dim d As Currency
a = 1
b = 1.15
c = 1.3
d = 1.45
p = Me.txtWeeklyPts1.Value
x = 100
Select Case Me.txtWeeklyPts1.Value
Case Is > 0 and <= 25
Me.txtPotentialPayout1.Value = 0
Case Is > 25 and <= 100
Me.txtPotentialPayout1.Value = p * a
Case Is > 100 and <= 200
Me.txtPotentialPayout1.Value = (x * a) + ((p - x) * b)
Case Is > 200 and <= 300
Me.txtPotentialPayout1.Value = (x * a) + (x * b) + ((p - (2 * x)) * c)
Case Is > 300
Me.txtPotentialPayout1.Value = (x * a) + (x * b) + ((p - (3 * x)) * d)
End Select
End Sub

 

[OlcayM]

ajetrumpet made the most of it... what you need is to change your code a little.

Private Sub txtWeeklyPts1_LostFocus()
Dim x As Long
Dim p As Long
Dim a As Currency
Dim b As Currency
Dim c As Currency
Dim d As Currency
a = 1
b = 1.15
c = 1.3
d = 1.45
p = Me.txtWeeklyPts1.Value
x = 100
Select Case Me.txtWeeklyPts1.Value
Case Is <= 25
Me.txtPotentialPayout1.Value = 0
Case Is <= 100
Me.txtPotentialPayout1.Value = p * a
Case Is <= 200
Me.txtPotentialPayout1.Value = (x * a) + ((p - x) * b)
Case Is <= 300
Me.txtPotentialPayout1.Value = (x * a) + (x * b) + ((p - (2 * x)) * c)
Case Else
Me.txtPotentialPayout1.Value = (x * a) + (x * b) + ((p - (3 * x)) * d)
End Select
End Sub

pls note that you should always ad a "case else" line for every select case statement, in case of none of the case statements satisfy...

 

[markey]

A big THANKS to the both of you. Thank you Oklay for cleaning it up, and thank you to ajetrumpet for providing the initial code/logic. It appears to be working 100%, and now I can work on the finishing touches and push this thing out. Thanks Again!!!