Hi all,
I have recently started cleaning up some of my bigger databases by refining the rediculous numbers of wueries ans writing them as functions. I have written a number of functions that have all been successful so far, though this one is a bit more advanced than the others.
This function is a linear regression based on results of a physical performance test called the chester step test.
The following code works great when i apply it on a form dealing with a single data record, but when added to a query with multiple records the calculation works fine but i eventually get the runtime error 6 overflow message. I'm aware that it may be related to declaring the wrong variable type, but i'm not sure which one.
Would appreciate if anyone has any clues as to how this code can be cleaned up or what variable types i may have incorrect.
Many thanks in advance for any assistance.
I have recently started cleaning up some of my bigger databases by refining the rediculous numbers of wueries ans writing them as functions. I have written a number of functions that have all been successful so far, though this one is a bit more advanced than the others.
This function is a linear regression based on results of a physical performance test called the chester step test.
The following code works great when i apply it on a form dealing with a single data record, but when added to a query with multiple records the calculation works fine but i eventually get the runtime error 6 overflow message. I'm aware that it may be related to declaring the wrong variable type, but i'm not sure which one.
Would appreciate if anyone has any clues as to how this code can be cleaned up or what variable types i may have incorrect.
Code:
Function VO2(StepHeight As Double, HR1 As Double, HR2 As Double, HR3 As Double, HR4 As Double, HR5 As Double, TestDate As Date, ClientID As Double) As Double
Dim L1 As Integer
Dim L2 As Integer
Dim L3 As Integer
Dim L4 As Integer
Dim L5 As Integer
Dim Pairs As Integer
Dim SumL As Integer
Dim MeanL As Double
Dim X1 As Double
Dim X2 As Double
Dim X3 As Double
Dim X4 As Double
Dim X5 As Double
Dim Y1 As Double
Dim Y2 As Double
Dim Y3 As Double
Dim Y4 As Double
Dim Y5 As Double
Dim SumXsq As Double
Dim SumYsq As Double
Dim SumP As Double
Dim M As Double
Dim B As Double
Dim DOB As Date
Dim Age As Integer
Dim MaxHR As Integer
'Calculating Age at date of assessment based on DOB of the client
DOB = DLookup("DOB", "tblClientDetails", "ClientID = " & ClientID)
Age = DateDiff("yyyy", DOB, TestDate) + CInt(Format(DOB, "mmdd") > Format(TestDate, "mmdd"))
MaxHR = 220 - Age
'Establishing X-Axis marker points based on the different step heights
If StepHeight = 15 Then
L1 = 11
L2 = 14
L3 = 18
L4 = 21
L5 = 25
Else
If StepHeight = 20 Then
L1 = 12
L2 = 17
L3 = 21
L4 = 25
L5 = 27
Else
If StepHeight = 25 Then
L1 = 14
L2 = 19
L3 = 24
L4 = 28
L5 = 33
Else
If StepHeight = 30 Then
L1 = 16
L2 = 21
L3 = 27
L4 = 32
L5 = 37
End If
End If
End If
End If
If HR4 = 0 And HR5 = 0 Then ''If both = 0 then only stage 3 was acheived, therefore regression only uses 3 points
Pairs = 3 ''Minimum 3 pairs required for regression
SumL = L1 + L2 + L3 ''Regression calculations
MeanL = SumL / Pairs
SumHR = HR1 + HR2 + HR3
MeanHR = SumHR / Pairs
X1 = L1 - MeanL
X2 = L2 - MeanL
X3 = L3 - MeanL
Y1 = HR1 - MeanHR
Y2 = HR2 - MeanHR
Y3 = HR3 - MeanHR
SumXsq = (X1 ^ 2) + (X2 ^ 2) + (X3 ^ 2)
SumYsq = (Y1 ^ 2) + (Y2 ^ 2) + (Y3 ^ 2)
SumP = (X1 * Y1) + (X2 * Y2) + (X3 * Y3)
M = SumP / SumXsq
B = MeanHR - (M * MeanL)
VO2 = (MaxHR - B) / M ''Using straight line formula y=mx+b rearranged as x=(y-b)/m
Else
If HR5 = 0 Then ''4 pairs used for regression
Pairs = 4
SumL = L1 + L2 + L3 + L4
MeanL = SumL / Pairs
SumHR = HR1 + HR2 + HR3 + HR4
MeanHR = SumHR / Pairs
X1 = L1 - MeanL
X2 = L2 - MeanL
X3 = L3 - MeanL
X4 = L4 - MeanL
Y1 = HR1 - MeanHR
Y2 = HR2 - MeanHR
Y3 = HR3 - MeanHR
Y4 = HR4 - MeanHR
SumXsq = (X1 ^ 2) + (X2 ^ 2) + (X3 ^ 2) + (X4 ^ 2)
SumYsq = (Y1 ^ 2) + (Y2 ^ 2) + (Y3 ^ 2) + (Y4 ^ 2)
SumP = (X1 * Y1) + (X2 * Y2) + (X3 * Y3) + (X4 * Y4)
M = SumP / SumXsq
B = MeanHR - (M * MeanL)
VO2 = (MaxHR - B) / M
Else
Pairs = 5 ''All 5 pairs used for regression
SumL = L1 + L2 + L3 + L4 + L5
MeanL = SumL / Pairs
SumHR = HR1 + HR2 + HR3 + HR4 + HR5
MeanHR = SumHR / Pairs
X1 = L1 - MeanL
X2 = L2 - MeanL
X3 = L3 - MeanL
X4 = L4 - MeanL
X5 = L5 - MeanL
Y1 = HR1 - MeanHR
Y2 = HR2 - MeanHR
Y3 = HR3 - MeanHR
Y4 = HR4 - MeanHR
Y5 = HR5 - MeanHR
SumXsq = (X1 ^ 2) + (X2 ^ 2) + (X3 ^ 2) + (X4 ^ 2) + (X5 ^ 2)
SumYsq = (Y1 ^ 2) + (Y2 ^ 2) + (Y3 ^ 2) + (Y4 ^ 2) + (Y5 ^ 2)
SumP = (X1 * Y1) + (X2 * Y2) + (X3 * Y3) + (X4 * Y4) + (X5 * Y5)
M = SumP / SumXsq
B = MeanHR - (M * MeanL)
VO2 = (MaxHR - B) / M
End If
End If
End Function
Many thanks in advance for any assistance.