Calculated Control Help Please!!! (1 Viewer)

[pcdummy]

I have a Calculated text Box [TotalEquity]
this can a return a positive or negative currency amout. OK to here; I arrive at this control by the following:
[Allowance]-[PayOff]=[TotalEquity]

I need to provide for a control that returns the negative value(if any) of [TotalEquity] and then place that value (as a positive amount) in [PayoffRollover]. I am trying code listed herein, and was wondering how can i use this (or something similar) in an expression to use as my control source.. Any ideas welcomed and appreciated..

If Me.TotalEquity < 0 Then
Me.PayoffRollover = Me.TotalPayoff -Me.TotalAllowance
Else
Me.PayoffRollover = 0
End If

Logic since [TotalEquity] is a negative amount then return the positive amount by using [Payoff]-[Allowance]=[PayoffRollover]... does not seem to work though.. anyone can tell me why.


Thanks a WHOLE BUNCH..

Jeff

 

[R. Hicks]

If you wish only to display (convert) the negative result as a positive result, you only need to use the ABS function in your code.

If Me.TotalEquity < 0 Then
Me.PayoffRollover = ABS(Me.TotalPayoff -Me.TotalAllowance)
Else
Me.PayoffRollover = 0
End If

HTH
RDH

[This message has been edited by R. Hicks (edited 06-30-2001).]

 

[Fornatian]

You could alternatively not do it VBA and just enter this in the controlsource of the unbound field:

Iif(Me![TotalEquity]<0,0,ABS(Me![TotalPayoff] -Me![TotalAllowance]))

 

[pcdummy]

Thanks guys for your quick replies; however still have no joy with either solution.. Tried the VBA Code on on current and Before_Update.. tried the expression as control source for [PayoffRollover].. any other ideas?

Thanks Again,

Jeff

 

[pcdummy]

Thanks for all the help guys. Fornattions Suggestion worked with a little modification. What I ended up with is this:

=IIf([TotalEquity]<0,([TotalPayoff]-[TotalAllowance]),0)

Works like a dream..
Thanks again guys..

Jeff

 

[Fornatian]

PC Dummy,

Can I ask are you saving the PayOffRollOver somewhere? You shouldn't be because you can calculate it from other data.

If you still want to (and its not advised) then you should move the calculation into the default value for the field and lock the field and disable it.

Ian

 

[pcdummy]

Thanks Ian

No I am not saving the amount any where. I have found that putting the calculation in the source works better for me though. If in the control source, then access won't let users change a calculated a control anyway.

Thanks again.

Jeff