Setting a maximum value in VBA code

[Kuleesha]

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.

Thanks in advance

Kuleesha

[Gasman]

Crossposted https://www.access-programmers.co.uk...in-vba.318746/

[SamT]

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

Example usage in your code:

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