Calculations not working in vba on form

[wwiSports]

I have the following procedure in a form:


Private Sub PlusMinus_Change()

PlusMinus = PmtAmt - Total
Select Case PlusMinus
Case Is < 0
PlusMinus.BackColor = vbRed
InvOnHold = &quot;Y&quot;
Case Is > 0
PlusMinus.BackColor = vbGreen
InvOnHold = &quot;&quot;
Case Is = 0
PlusMinus.BackColor = -2147483633
InvOnHold = &quot;&quot;
End Select

InvChange = True

End Sub 'PlusMinus_Change

I am trying to calculate if the customer paid the correct amount, if they didn't pay enough I want the box to be red, if they paid to much I want the box to be green, if they paid the correct amount I want the box to be grey.

The box with the above code is showing up grey. The calculation is not showing up correctly. Can someone help?

Beth

 

[FoxProProgrammer]

Hi Beth,

My first guess is that PmtAmt or Total doesn't have the correct value when the procedure executes. Have you run in debug mode to see what value they have when it gets to the calculation? Where do these values come from? Are they fields on the form or data from a table or query?

Just as a side note, I haven't seen colors specified with vbRed, vbGreen, etc. That doesn't mean it doesn't exist; I just haven't seen that and couldn't find it in the Help. I learn something new every day though! I have used the RGB function to specify colors. For example:

Dim backRed As Long
Dim backGreen As Long


backRed = RGB(255, 0, 0)
backGreen = RGB(0, 255, 0)

PlusMinus.BackColor = backRed

Once you get this working, you might also need to change the ForeColor if the text doesn't show up well against the red or green background.




dz
dzaccess@yahoo.com

 

[wwiSports]

Actually, the color's above work. (vbRed, vbGreen--it's in Access 97). They have a database in use with those for a year now. I am revamping that database now though, actually pulling two together.

The real problem is this. When The PmtAmt and the Total are =, it is showing up as 0.01 in the PlusMinus Box with the above code.

How can I change that.

When the Total is greater than or less than the PmtAmt, it is showing up correctly. The only time it shows up as 0.01 is when the PmtAmt and the Total are equal

Sorry, I dont know how to use the debug window very well.

Beth

 

[FoxProProgrammer]

Thanks for letting me know about vbRed, etc...I finally found them in the Help. They are constants. Access also defines contants for vbBlack, vbBlue, vbYellow, vbMagenta, vbCyan, and vbWhite.

The problem that you are describing now sounds like a rounding issue. You can correct it by changing:

Select Case PlusMinus

to

Select Case Round(PlusMinus, 1)

The only problem with this is that any difference between -0.10 and +0.10 will convert to 0. The only other way that I can think of getting around the rounding error is to have a case for 0.01 and -0.01. This would work as long as the rounding error is always +/-1 cent, or if you don't care if a real 1 cent difference is treated as 0. You might even decide that a dime is close enough to 0 to use the Round function. You can force the text box to display 0 by assigning plusminus to 0 in the path case Is = 0
dz
dzaccess@yahoo.com

 

[Rolliee]

Beth

It is often easier to debug with msgbox window. I would use it to debug the values and the vartype's. The texts boxes on forms are just that - text boxes. To convert use val() function or even better, use variables of the type currency to calculate the two values. Then I believe the mils (fractions of a penny) are not considered.

rollie@bwsys.net

 

[MichaelRed]

In general, rollie appears to be on the correct route. The difference is PROBABLY due to the different data types. HOWEVER - the difference for the data types should NOT be this large. Typical differences are in the range of 10^-5 (0.0000n). In a few casual attempts to recreate this difference in 'debug', I did not generate any difference in values which would typically be entered for currency. The suspect here is the accumulation of the &quot;[Total]&quot; is being calculated with mixed field types (one currency and one single?) , and/or the application is a series of payments aginst this &quot;[Total]&quot;. since the data types are different and there is a series of payments, the total will never actually get to zero. IF, all data types used are currency, you stand a BETTER chance of actually reaching Zero. The more general soloution would be to use the range (between -0.01 and 0.01) as the criteria. You will need to get the beans dept approval for this, and they may want different limits even if they eventually do approve.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[cmmrfrds]

I concur with Rolliee and MichaelRed the difference is due to the data type. One of the data types must be a floating type which only equals zero by coincidence unless rounded to an integer. An Access &quot;double&quot; data type is a floating number. To avoid this problem, you should not have an amount field for currency purposes as a floating number - use currency instead of double. Now, you still may not reach zero if you group records as sub totals (non-integer) and then compare the summation of sub totals to an overall total, but it does not appear you are doing that.

 

[wwiSports]

I am running access 97, and the round feature is not working. I have changed all of the dollar items in the code to currency, but i'm still running into an issue like below:

SubTotal: 9.00
Tax: 0.41
Total: 9.41

PmtAmt: 9.41

PlusMinus= 0.01 (Should be 0.00)

Any other suggestions?

 

[MichaelRed]

O.K. One MORE shot. Using the CASE structure, the FIRST true condition is executed, and hte structure 'exits'. since 0 is less than 0.01, the color would (in Your scenario) &quot;vbRed&quot; whenever the total was zero -which (again is less than zero) ...

Private Sub PlusMinus_Change()
      
    PlusMinus = PmtAmt - Total
    Select Case PlusMinus
        Case Is = 0
        PlusMinus.BackColor = -2147483633
        InvOnHold = ""
        Case Is <= 0.01
        PlusMinus.BackColor = vbRed
        InvOnHold = &quot;Y&quot;
        Case Is >= 0.01
        PlusMinus.BackColor = vbGreen
        InvOnHold = &quot;&quot;
    End Select
    
    InvChange = True
    
End Sub  'PlusMinus_Change

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over