User Defined Function - Not returning expected value (1 Viewer)

[caprice63]

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])

 

[theDBguy]

Hi,

First of all, your function returns an Integer, but the result you expect (41.27% or .4127) is not an Integer. Also, I think the Val() function returns an Integer as well.

 

[The_Doc_Man]

Agree with DBGuy.

Returning a fraction as type INTEGER will truncation to the right of the decimal, so 0.4127 truncates to 0.

ALSO... what data type is used for MinOfRTGRScore and MaxofRTGRScore? Unless these are strings, you don't need a VAL at all, because Variants can take on any numeric value. IF they are text, instead of using VAL, use something like CDBL()

 

[June7]

Declare the function As Double.

 

[caprice63]

Thanks for the quick answer.

Declaring the function As Double worked a trreat.

 

[Galaxiom]

It is a good practice to avoid Variants as parameters and deal with the potential for Nulls before passing the argument using Nz().

Also declare the parameters as ByVal. This makes it clear that they will not be modified by the function. By default they are passed ByRef and changes to the parameter in the function will be passed back.