'Undefined function in expression' error

[Guest]

Hi

I'm a VB beginner using MS Access 2002

I'm trying to use the Haversine formula to calculate distances between
geographic locations (lat longs) contained in a database. I have written a
module to do this - the module contains trig and compiles OK. When I try to
use this module in a query I get an 'undefined function in expression' error.

Any suggestions would be appreciated.

My Haversine module;

Option Explicit

Const r As Integer = 6367
Dim lat1 As Double
Dim lat2 As Double
Dim lon1 As Double
Dim lon2 As Double
Dim dlat As Double
Dim dlon As Double
Dim A As Double
Dim C As Double
Dim d As Double

Public Function Haversine(lat1, lon1, lat2, lon2) As Double

dlat = lat2 - lat1
dlon = lon2 - lon1
A = ((Sin(dlat / 2)) * (Sin(dlat / 2))) + Cos(lat1) * Cos(lat2) * ((Sin(dlon
/ 2)) * (Sin(dlon / 2)))
C = 2 * Atn(A / Sqr(-A * A + 1))
d = r * C

Haversine = d

End Function

 

[storrboy]

Three things come to mind.
Is this a standard or class module the function is in?
What is the syntax you are using in the query?
Are all refrences unbroken? I have encountered missing references
causing core math functions to fail.

 

[John Nurick]

First, have you tested your function outside a query and made sure that
it produces the correct values?

To test it in a query, start by creating a query that calls the function
but returns just one or two pairs of lat, lon coordinates. Open the VBA
module and click in the left margin to set a breakpoint on the first
line of the Haversine function. Then when you run the query the function
will stop at that point so you can step through line by line and
discover where the problem is.

Other sugesstions inline.

Hi

I'm a VB beginner using MS Access 2002

I'm trying to use the Haversine formula to calculate distances between
geographic locations (lat longs) contained in a database. I have written a
module to do this - the module contains trig and compiles OK. When I try to
use this module in a query I get an 'undefined function in expression' error.

Any suggestions would be appreciated.

My Haversine module;

If the module's name is "Haversine", change it: a module can't have the
same name as any of the functions or subroutines it contains.

Option Explicit

Const r As Integer = 6367
Dim lat1 As Double
Dim lat2 As Double
Dim lon1 As Double
Dim lon2 As Double

The following variable declarations (Dim) should be in the function.
Otherwise they will be shared between all procedures in the module,
which is sometimes necessary but usually is just a possible source of
bugs.

Dim dlat As Double
Dim dlon As Double
Dim A As Double
Dim C As Double
Dim d As Double

Is "Haversine" the right name for the function? It doesn't return a
haversine but a distance in km. Might this cause confusion later?

Public Function Haversine(lat1, lon1, lat2, lon2) As Double

You're declaring the lat1, lon1... arguments with no "As" to specify
data type. This means they'll be Variants. IMHO it's usually better to
say so explicitly:

Public Function Haversine(lat1 As Variant, lon1 As Variant _
lat2 As Variant, lon2 As Variant)_
As Double

Normally you would want the return value for a double, but if you're
calling the function in a query you may want to cover the possibility of
a null value in one of the lat or lon arguments by returning a null
value then. So when I'm writing a function for use in queries I normally
declare it
As Variant

Declarations go here.

At this point, I'd check that all the values are present:

If IsNull(lat1) Or IsNull(lon1) Or IsNull(lat2) Or IsNull(lon2) Then
'we don't have all the arguments we need.
Haversine = Null
Exit Function
End If

and maybe also check that all the values are plausible, e.g. latitudes
between -pi/2 and +pi/2 and longitudes between -pi and +pi.

 

[Guest]

Hi John,

Many thanks for your advice. Partial victory - My query now returns a
column of numerical values that vary in the right direction but are incorrect
(almost certainly due to an error in my translation of the Haversine
formula). When I have corrected the math I will post the correct version to
the group.

Regards,

Squinado

 

[James A. Fortune]

Hi

I'm a VB beginner using MS Access 2002

I'm trying to use the Haversine formula to calculate distances between
geographic locations (lat longs) contained in a database. I have written a
module to do this - the module contains trig and compiles OK. When I try to
use this module in a query I get an 'undefined function in expression' error.

Any suggestions would be appreciated.

My Haversine module;

Option Explicit

Const r As Integer = 6367
Dim lat1 As Double
Dim lat2 As Double
Dim lon1 As Double
Dim lon2 As Double
Dim dlat As Double
Dim dlon As Double
Dim A As Double
Dim C As Double
Dim d As Double

Public Function Haversine(lat1, lon1, lat2, lon2) As Double

dlat = lat2 - lat1
dlon = lon2 - lon1
A = ((Sin(dlat / 2)) * (Sin(dlat / 2))) + Cos(lat1) * Cos(lat2) * ((Sin(dlon
/ 2)) * (Sin(dlon / 2)))
C = 2 * Atn(A / Sqr(-A * A + 1))
d = r * C

Haversine = d

End Function

Using the Law of Cosines, a standard haversine turns out to be
proportional to the straight line distance between the two points. You
probably don't want that. See:

http://groups.google.com/group/microsoft.public.access/browse_frm/thread/b7baa12e5809aa90

for a way to get the great circle distance between the two points.

James A. Fortune
(e-mail address removed)