Custom Function behaving strangely in query

[DamianC-UK]

I have a function to calculate a score, which is used in my query.

It looks at a percentage, and returns a 1-10 score based on the percentage
returned.

The function is as follows:
Function myScore(data as double, reverseorder as boolean) as integer
'calculations to return a 1-10 score in here, but not relevant to the
problem
End function

The "data" variable is fed in in the query as follows:
Score: myscore(difference,true)
The "difference" is a percentage, but has been left in its raw form in the
calculation, so it would appear as 0.05 for 5% - these numbers are division
calculations, and are at quite a high decimal precision.

However, and this is the frustrating part, when that number, which appears
in the query as 0.05465455432127 (for example), is pulled into the function,
it multiplies it up to become 5.465455432127.

I've tried everything I can think of to keep the number at the right data
type, and have scoured the web for similar problems, but no matter what I do,
it goes wrong.
I would simply divide by 100, but in some queries where this is used it
pulls through fine, in others it doesn't. There's no consistency - it appears
to be the query, not the function.

Only other thing I can think of which could cause this problem is related to
the numbers that generate the percentage, which use the nz() function to
check that there are is no blank data/divide by zero's being produced.

Has anyone any ideas as to what could cause this, or a workaround to fix
this problem?

Any help would be much appreciated, I've been tearing my hair out over this
for the last 8 hours!!

 

[Clifford Bass]

Hi,

I think your idea about the Nz() function and whatnot as the cause may
be accurate. Nz() does not maintain data types. Try using IIf() with
IsNull(), possibly even with the CDbl() function, instead. So if SomeColumn
is defined as a double number:

IIf(IsNull([SomeColumn]), CDbl(0), [SomeColumn])

Hope that helps,

Clifford Bass