Text Box Calculation Formula

  • Thread starter Thread starter Tom Perot
  • Start date Start date
T

Tom Perot

SpreadCred
InsideSpread
LeftMain
RightMain

I have a text box (SpreadCred) that needs to be calculated/populated by a
combination of factors.

1. SpreadCred should populate off of the text box - InsideSpread.

2. The value in SpreadCred can not be greater than text box - RightMain nor
text box - LeftMain. If it is bigger than either number, it will max out at
the larger of the two.

Thanks for your time.
 
How this is done depends a little on whether the value will respond to user
input or to previously-entered data.

This could be done with a nested IIf, but is really a candidate for a custom
function call. Assuming all three values are long integer types, do this;
replace Long by Double or other data type as required:

Make this the ControlSource of SpreadCred

=CalcValue(InsideSpread,LeftMain,RightMain)

Add this function to the module for the form or report:

Private Function CalcValue(varInsideSpread as Variant, varLeftMain as
Variant, varRightMain as Variant) as Variant
If IsNull(varInsideSpread ) or IsNull(varLeftMain) or IsNull(varRightMain )
Then
CalcValue = Null
Else
CalcValue = lngInsideSpread
If CalcValue > lngLeftMain Then CalcValue = lngLeftMain
If CalcValue < lngRightMain and lngRightMain < lngInsideSpread Then
CalcValue = lngRightMain
End If
End Function

The If IsNull just ensures that the value will be blank until all three
prerequisite values are populated. You will need to use NZ if you want to
assume 0 for nulls.
 
Here are the properties for the fields
Field size: DECIMAL
Format: GENERAL NUMBER
Precision: 18
Scale: 3
Decimal Places: 3

How will this change the programming?
 
Back
Top