# Thread: Setting a maximum value in VBA code

1. ## Setting a maximum value in VBA code

Hi,
I'm new to vba on access.

I'm trying to set up a calculation for a score called MELD score where MELD Score = (0.957 * ln(Creatinine) + 0.378 * ln(Bilirubin) + 1.120 * ln(INR) + 0.643 ) * 10

I've set up the coding on a command button (MELD calc) as follows:

Private Sub Meld_calc_Click()
'Stop

Dim LnCrea
LnCrea = Log(Me.Creatinine)
Debug.Print LnCrea

Dim LnTbil
LnTbil = Log(Me.Bilirubin)
Debug.Print LnTbil

Dim LnINR
LnINR = Log(Me.INR)
Debug.Print LnINR

Me.MELD = ((0.957 * LnCrea) + (0.378 * LnTbil) + (1.12 * LnINR) + 0.643) * 10

Debug.Print Me.MELD

End Sub

I get good results within a certain range of values with this but there are several problems:
1. There is a cap for Creatinine value used in the MELD score (capped at 4)
2. The total for MELD is capped at 40
3. Lower limit for each of the 3 components are set at 1

I am unable to figure out the additional coding needed to get the above requirements fulfilled

I would be extremely grateful for any suggestions.

Kuleesha  Reply With Quote

2.  Reply With Quote

3. I don't do Access, but I didn't see a min or Max functions, and I don't understand DMin and DMax, but these functions will act as Min and as Max functions
```Private Function Cap(Limit As Double, Value As Double) As Double
If Limit > Value Then
Cap = Value
Else
Cap = Limit
End If
End Function```
```Private Function LowerLimit(Lowest As Double, Value As Double) As Double
If Lowest < Value Then
LowerLimit = Value
Else
LowerLimit = Lowest
End If
End Function```
```Dim NewCrea, NewTbil, NewINR

NewCrea = LowerLimit(1, Me.Creatinine) 'Also for NewTbil, NewINR
NewCrea = Cap(4, NewCrea )

LnCrea = Log(NewCrea) 'Also for LnTbil, LnINR

Me.Meld = Cap(40, ((0.957 * LnCrea) + (0.378 * LnTbil) + (1.12 * LnINR) + 0.643) * 10```  Reply With Quote

upper limit; lower limit 