Function in Query is "too complex"

[Guest]

The function calculates the distance between two latitude/longitude pairs.
The result is either -1 or the distance AS Single. (find zip codes within
'x' miles of another zip code, return -1 if too far away)

The "complex" error only occurs when the function, FCN, references fields in
the query and a constraint is placed on that value. the erorr does not occur
when additional constraints are placed on the function....

pseudo SQL:
SELECT zip, lat, lng, FCN(lat, lng)
FROM tblZipCodes
WHERE ((not isnull(lat)) AND (not isnull(lng)) AND (FCN(lat,lng))>-1);

I have tried appending functions to FCN, NZ, CSng, and CDbl, to 'force' a
number, but no luck.

What suggestions do you folks have?
thank you

 

[Duane Hookom]

Try something like:
SELECT zip, lat, lng, FCN(Nz(lat,90), Nz(lng, 100))
FROM tblZipCodes
WHERE FCN(Nz(lat,90), Nz(lng, 100))>-1);

 

[Guest]

Duane: you are always correct. Thanks for your help.

BUT why?? I do not see the logical difference, in the two statements...
does the function run 'before' the null values are removed? If so, this
would make the query, well, um "Complex".

Since no null values will be passed to the funtion using Not IsNull(x)
WHERE ((not isnull(lat)) AND (not isnull(lng)) AND (FCN(lat,lng))>-1

Why does embedding Nz(x,y) allow the function to operate 'properly'?
WHERE FCN(Nz(lat,90), Nz(lng, 100))>-1)

 

[Duane Hookom]

I'm not sure the order that expressions are evaluated. I would take special
precautions to make sure nulls don't get sent to FCN(). To me, it just makes
sense to avoid datatype issues before they happen.