Function Problem

[ndeans]

I have written the following simple function to determine the 'Best' score of three attempts. The number can range from -30 to +30 or so and are measured to 0.5 intervals.

Function FlexBest(a As Double, b As Double, c As Double) As Double
    If CDec(Nz(a)) >= CDec(Nz(b)) And CDec(Nz(a)) >= CDec(Nz(c)) Then
        FlexBest = CDec(Nz(a))
    ElseIf CDec(Nz(b)) >= CDec(Nz(a)) And CDec(Nz(b)) >= CDec(Nz(c)) Then
        FlexBest = CDec(Nz(b))
    Else
        FlexBest = CDec(Nz(c))
    End If

This function works perfectly when running it in a query with no hassles what so ever.

But

When i try and run it as an 'AfterUpdate' or 'OnCurrent' event on a form there is some funny rounding going on.

eg three values of -1, 3, and 5.5 will return as a 6 rather than a 5.5 while three values of -1, 3, 5 will always return as 5.

I have noticed in the vb section of the form when i try to write the function for some reason the variables are being changed from doubles to longs. I beleive this is probably the source of my problem but i can't work out how to fix it.

eg when i try this:

txtBest = FlexBest([txtFlexOne], [txtFlexTwo], [txtFlexThree])

The autoprompt thing kicks in and starts this

[I]txtBest = FlexBest(a As Long, b As Long, c As Long) As Long[/I]

I can't work out why the variable appear to be changed from double to long, given the function is located in a functions module.

All help welcome and very much appreciated.

Cheers
Nathan

 

[HiTechCoach]

Do you also have the function "behind the form"? This is the code you get if you view the Code for the form.

 

[Galaxiom]

You have not declared the variables.

"..... in order to use the Decimal data type, you must declare a variable of type Variant, and then convert it by using the CDec function."

http://msdn.microsoft.com/en-us/library/aa164511(office.10).aspx


More to the point, why are you using the CDec function at all? This converts the variable to a scaled integer is is used for maintaining accuracy by avoiding rounding errors.

Why are you using Double as the data type for an argument that can only have single decimal place? Ideally if possible you should rescale your scores to work with integers.

Note also that the CDec function can only return a Variant data type.

Your function also has a logical problem in that if one of your arguments evaluates to a null while the others are both negative you will get zero result rather than the highest negative (closest to zero) value.

 

[ndeans]

Solved Guys, seems i had an old version of the function on the form so it was defaulting to that function rather than the one in the module.
I have to admit i'm a little new to the functions side of things, particularly the variant types, really atm i'm just burglarising what i'm familiar with for the time being.
Also regarding the logic of the function and the Null figures, i agree, it's something i scratched my head on when writing it so i figured for the moment i'll write it as is and make sure i don't leave any nulls to cause errors. So far on the very rare occasion i get a null value i just repeat the best entry a second time. Hardly good coding but it serves the purpose until i can address it properly.

Again many thanks for your input people, and i promise i'm working on cleaning up my code...

Cheers
Nathan

 

[HiTechCoach]

Glad to hear you have it working ...