I've been trying to use some UDF's and have got a small problem.
The Function does not return the value I calculate manually.
I think it may be something to do with the syntax of the arguments and would welcome help to sort it please.
My Query Table is "Today_Time_Track_b" which is made up from 4 other Query Tables (Today_Time_Track_a, MaxFields_Time_Track, MaxJR and MinJR). MaxJR and MinJR are not relevant to this calculation.
Using Builder the following expression provides the correct answer:
Expr3: ([Today_Time_Track_a]![TRGT]-[MaxFields_Time_Track]![MinOfRGTRScore])/([MaxFields_Time_Track]![MaxOfRGTRScore]+1-[MaxFields_Time_Track]![MinOfRGTRScore])
Answer = 41.27%
However, the UDF returns an answer of 0.00%
This is the UDF:
Option Compare Database
Option Explicit
Public Function FnRGTR(TRGT As Variant, MinOfRGTRScore As Variant, MaxOfRGTRScore As Variant) As Integer
TRGT = Val(TRGT & "")
MinOfRGTRScore = Val(MinOfRGTRScore & "")
MaxOfRGTRScore = Val(MaxOfRGTRScore & "")
FnRGTR = 0
If TRGT <= 0 Then
FnRGTR = 0.5
Else
FnRGTR = (TRGT - MinOfRGTRScore) / (MaxOfRGTRScore + 1 - MinOfRGTRScore)
End If
End Function
Builder entry:
Expr2: FnRGTR([TRGT],[MinOfRGTRScore],[MaxOfRGTRScore])
The Function does not return the value I calculate manually.
I think it may be something to do with the syntax of the arguments and would welcome help to sort it please.
My Query Table is "Today_Time_Track_b" which is made up from 4 other Query Tables (Today_Time_Track_a, MaxFields_Time_Track, MaxJR and MinJR). MaxJR and MinJR are not relevant to this calculation.
Using Builder the following expression provides the correct answer:
Expr3: ([Today_Time_Track_a]![TRGT]-[MaxFields_Time_Track]![MinOfRGTRScore])/([MaxFields_Time_Track]![MaxOfRGTRScore]+1-[MaxFields_Time_Track]![MinOfRGTRScore])
Answer = 41.27%
However, the UDF returns an answer of 0.00%
This is the UDF:
Option Compare Database
Option Explicit
Public Function FnRGTR(TRGT As Variant, MinOfRGTRScore As Variant, MaxOfRGTRScore As Variant) As Integer
TRGT = Val(TRGT & "")
MinOfRGTRScore = Val(MinOfRGTRScore & "")
MaxOfRGTRScore = Val(MaxOfRGTRScore & "")
FnRGTR = 0
If TRGT <= 0 Then
FnRGTR = 0.5
Else
FnRGTR = (TRGT - MinOfRGTRScore) / (MaxOfRGTRScore + 1 - MinOfRGTRScore)
End If
End Function
Builder entry:
Expr2: FnRGTR([TRGT],[MinOfRGTRScore],[MaxOfRGTRScore])