Function in query causes loop?

B

BusterB

Hi,

I have followed this newsgroup for years and have never needed to post a
question until now as someone has usually had the same question answered
before. But....

Below is a function I wrote that calculates the bearing between two
positions given in latitude and longitude (stored as minutes N and E for
positive numbers)

When I test the function in the immediate window it works fine.

When I include the function in a query field, it sends the query into a
never-ending loop of calculations.

I have a similar Function that calulates the Distance between the two
positions and it works fine.

What is wrong???

Here is my function -

Function calcBrg(sLat As Single, sLong As Single, fLat As Single, fLong As
Single) As Single

Dim dLat As Single 'difference in lat
Dim dLong As Single 'difference in long
Dim mLat As Single 'mean latitude
Dim xLat As Single
Dim aBrg As Single
Dim finalBrg As Single

On Error GoTo calcBrg_Error

DoCmd.Echo False

'co-ord stored as minutes, convert to deg before use
sLat = sLat / 60
sLong = sLong / 60
fLat = fLat / 60
fLong = fLong / 60

If sLat = 0 Then
'no position

Else

dLat = fLat - sLat 'difference in latitude

'If difference = 0 then make it negligable
If dLat = 0 Then
dLat = 0.000000001
End If

dLong = fLong - sLong 'difference in longitude
mLat = (sLat + fLat) / 2 'find mean latitude
mLat = mLat * (Pi / 180) 'convert mean lat to radians
xLat = Cos(mLat) * dLong
xLat = (xLat / dLat)
aBrg = rad2deg(Atn(xLat))


If dLat < 0 Then
finalBrg = aBrg + 180
ElseIf aBrg < 0 Then
finalBrg = aBrg + 360
Else
finalBrg = Nz(aBrg)
End If
End If

calcBrg = finalBrg
'Debug.Print "calcBrg = " & calcBrg

DoCmd.Echo True

EndCode:
DoCmd.Echo True

On Error GoTo 0
Exit Function

Exit_calcBrg:
DoCmd.Echo True
Exit Function

calcBrg_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
calcBrg of Module basBearingAndRange"
Resume Exit_calcBrg


End Function


And here is the query i am using it in -

SELECT qryResources.ResourceSpecID, qryResources.BaseName,
qryResources.BaseLat, qryResources.BaseLong,
Format(calcRange(Forms!frmMainDetails!Lat,Forms!frmMainDetails!Long,[BaseLat],[BaseLong],"N"),"Fixed")
AS Range, qryResources.BaseActive,
Format(calcBrg([InstallationLat],[InstallationLong],[BaseLat],[BaseLong]),"000")
AS Brg, qryAllDetails.qryMainDetails.GroupID, qryAllDetails.Lat AS
InstallationLat, qryAllDetails.Long AS InstallationLong
FROM qryResources, qryAllDetails
WHERE
(((qryAllDetails.qryMainDetails.GroupID)=[Forms]![frmMainDetails]![InstallationID]))
ORDER BY
Format(calcRange(Forms!frmMainDetails!Lat,Forms!frmMainDetails!Long,[BaseLat],[BaseLong],"N"),"Fixed");


I am pulling my hair out now. All advice welcome. TIA

Kevin
 
K

KARL DEWEY

Maybe you need to join qryResources and qryAllDetails in your FROM statement
because you are creating a Cartesian effect.

--
Build a little, test a little.


BusterB said:
Hi,

I have followed this newsgroup for years and have never needed to post a
question until now as someone has usually had the same question answered
before. But....

Below is a function I wrote that calculates the bearing between two
positions given in latitude and longitude (stored as minutes N and E for
positive numbers)

When I test the function in the immediate window it works fine.

When I include the function in a query field, it sends the query into a
never-ending loop of calculations.

I have a similar Function that calulates the Distance between the two
positions and it works fine.

What is wrong???

Here is my function -

Function calcBrg(sLat As Single, sLong As Single, fLat As Single, fLong As
Single) As Single

Dim dLat As Single 'difference in lat
Dim dLong As Single 'difference in long
Dim mLat As Single 'mean latitude
Dim xLat As Single
Dim aBrg As Single
Dim finalBrg As Single

On Error GoTo calcBrg_Error

DoCmd.Echo False

'co-ord stored as minutes, convert to deg before use
sLat = sLat / 60
sLong = sLong / 60
fLat = fLat / 60
fLong = fLong / 60

If sLat = 0 Then
'no position

Else

dLat = fLat - sLat 'difference in latitude

'If difference = 0 then make it negligable
If dLat = 0 Then
dLat = 0.000000001
End If

dLong = fLong - sLong 'difference in longitude
mLat = (sLat + fLat) / 2 'find mean latitude
mLat = mLat * (Pi / 180) 'convert mean lat to radians
xLat = Cos(mLat) * dLong
xLat = (xLat / dLat)
aBrg = rad2deg(Atn(xLat))


If dLat < 0 Then
finalBrg = aBrg + 180
ElseIf aBrg < 0 Then
finalBrg = aBrg + 360
Else
finalBrg = Nz(aBrg)
End If
End If

calcBrg = finalBrg
'Debug.Print "calcBrg = " & calcBrg

DoCmd.Echo True

EndCode:
DoCmd.Echo True

On Error GoTo 0
Exit Function

Exit_calcBrg:
DoCmd.Echo True
Exit Function

calcBrg_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
calcBrg of Module basBearingAndRange"
Resume Exit_calcBrg


End Function


And here is the query i am using it in -

SELECT qryResources.ResourceSpecID, qryResources.BaseName,
qryResources.BaseLat, qryResources.BaseLong,
Format(calcRange(Forms!frmMainDetails!Lat,Forms!frmMainDetails!Long,[BaseLat],[BaseLong],"N"),"Fixed")
AS Range, qryResources.BaseActive,
Format(calcBrg([InstallationLat],[InstallationLong],[BaseLat],[BaseLong]),"000")
AS Brg, qryAllDetails.qryMainDetails.GroupID, qryAllDetails.Lat AS
InstallationLat, qryAllDetails.Long AS InstallationLong
FROM qryResources, qryAllDetails
WHERE
(((qryAllDetails.qryMainDetails.GroupID)=[Forms]![frmMainDetails]![InstallationID]))
ORDER BY
Format(calcRange(Forms!frmMainDetails!Lat,Forms!frmMainDetails!Long,[BaseLat],[BaseLong],"N"),"Fixed");


I am pulling my hair out now. All advice welcome. TIA

Kevin
Click to expand...
 
B

BusterB

Hi Karl,

Thanks for your reply, but I cannot join the two queries. I want to have
the bearings and distances calculated between all records in both tables.

Then I was going to filter the records using qryAllDetails.GroupID to show
just one position from qryAllDetails and then show all the calculated
bearings and distances from qryResources.

Am I making it clear what I want. The two queries are not linked in any way
apart from they both contain positions in them. I want a query that takes the
position info in qryAllDetails and calculates the bearings and ranges to or
from the positions in qryResources.

Thanks.

KARL DEWEY said:
Maybe you need to join qryResources and qryAllDetails in your FROM statement
because you are creating a Cartesian effect.

--
Build a little, test a little.


BusterB said:
Hi,

I have followed this newsgroup for years and have never needed to post a
question until now as someone has usually had the same question answered
before. But....

Below is a function I wrote that calculates the bearing between two
positions given in latitude and longitude (stored as minutes N and E for
positive numbers)

When I test the function in the immediate window it works fine.

When I include the function in a query field, it sends the query into a
never-ending loop of calculations.

I have a similar Function that calulates the Distance between the two
positions and it works fine.

What is wrong???

Here is my function -

Function calcBrg(sLat As Single, sLong As Single, fLat As Single, fLong As
Single) As Single

Dim dLat As Single 'difference in lat
Dim dLong As Single 'difference in long
Dim mLat As Single 'mean latitude
Dim xLat As Single
Dim aBrg As Single
Dim finalBrg As Single

On Error GoTo calcBrg_Error

DoCmd.Echo False

'co-ord stored as minutes, convert to deg before use
sLat = sLat / 60
sLong = sLong / 60
fLat = fLat / 60
fLong = fLong / 60

If sLat = 0 Then
'no position

Else

dLat = fLat - sLat 'difference in latitude

'If difference = 0 then make it negligable
If dLat = 0 Then
dLat = 0.000000001
End If

dLong = fLong - sLong 'difference in longitude
mLat = (sLat + fLat) / 2 'find mean latitude
mLat = mLat * (Pi / 180) 'convert mean lat to radians
xLat = Cos(mLat) * dLong
xLat = (xLat / dLat)
aBrg = rad2deg(Atn(xLat))


If dLat < 0 Then
finalBrg = aBrg + 180
ElseIf aBrg < 0 Then
finalBrg = aBrg + 360
Else
finalBrg = Nz(aBrg)
End If
End If

calcBrg = finalBrg
'Debug.Print "calcBrg = " & calcBrg

DoCmd.Echo True

EndCode:
DoCmd.Echo True

On Error GoTo 0
Exit Function

Exit_calcBrg:
DoCmd.Echo True
Exit Function

calcBrg_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
calcBrg of Module basBearingAndRange"
Resume Exit_calcBrg


End Function


And here is the query i am using it in -

SELECT qryResources.ResourceSpecID, qryResources.BaseName,
qryResources.BaseLat, qryResources.BaseLong,
Format(calcRange(Forms!frmMainDetails!Lat,Forms!frmMainDetails!Long,[BaseLat],[BaseLong],"N"),"Fixed")
AS Range, qryResources.BaseActive,
Format(calcBrg([InstallationLat],[InstallationLong],[BaseLat],[BaseLong]),"000")
AS Brg, qryAllDetails.qryMainDetails.GroupID, qryAllDetails.Lat AS
InstallationLat, qryAllDetails.Long AS InstallationLong
FROM qryResources, qryAllDetails
WHERE
(((qryAllDetails.qryMainDetails.GroupID)=[Forms]![frmMainDetails]![InstallationID]))
ORDER BY
Format(calcRange(Forms!frmMainDetails!Lat,Forms!frmMainDetails!Long,[BaseLat],[BaseLong],"N"),"Fixed");


I am pulling my hair out now. All advice welcome. TIA

Kevin
Click to expand...
Click to expand...
 
K

KARL DEWEY

Try a union query then the calculations.
--
Build a little, test a little.


BusterB said:
Hi Karl,

Thanks for your reply, but I cannot join the two queries. I want to have
the bearings and distances calculated between all records in both tables.

Then I was going to filter the records using qryAllDetails.GroupID to show
just one position from qryAllDetails and then show all the calculated
bearings and distances from qryResources.

Am I making it clear what I want. The two queries are not linked in any way
apart from they both contain positions in them. I want a query that takes the
position info in qryAllDetails and calculates the bearings and ranges to or
from the positions in qryResources.

Thanks.

KARL DEWEY said:
Maybe you need to join qryResources and qryAllDetails in your FROM statement
because you are creating a Cartesian effect.

--
Build a little, test a little.


BusterB said:
Hi,

I have followed this newsgroup for years and have never needed to post a
question until now as someone has usually had the same question answered
before. But....

Below is a function I wrote that calculates the bearing between two
positions given in latitude and longitude (stored as minutes N and E for
positive numbers)

When I test the function in the immediate window it works fine.

When I include the function in a query field, it sends the query into a
never-ending loop of calculations.

I have a similar Function that calulates the Distance between the two
positions and it works fine.

What is wrong???

Here is my function -

Function calcBrg(sLat As Single, sLong As Single, fLat As Single, fLong As
Single) As Single

Dim dLat As Single 'difference in lat
Dim dLong As Single 'difference in long
Dim mLat As Single 'mean latitude
Dim xLat As Single
Dim aBrg As Single
Dim finalBrg As Single

On Error GoTo calcBrg_Error

DoCmd.Echo False

'co-ord stored as minutes, convert to deg before use
sLat = sLat / 60
sLong = sLong / 60
fLat = fLat / 60
fLong = fLong / 60

If sLat = 0 Then
'no position

Else

dLat = fLat - sLat 'difference in latitude

'If difference = 0 then make it negligable
If dLat = 0 Then
dLat = 0.000000001
End If

dLong = fLong - sLong 'difference in longitude
mLat = (sLat + fLat) / 2 'find mean latitude
mLat = mLat * (Pi / 180) 'convert mean lat to radians
xLat = Cos(mLat) * dLong
xLat = (xLat / dLat)
aBrg = rad2deg(Atn(xLat))


If dLat < 0 Then
finalBrg = aBrg + 180
ElseIf aBrg < 0 Then
finalBrg = aBrg + 360
Else
finalBrg = Nz(aBrg)
End If
End If

calcBrg = finalBrg
'Debug.Print "calcBrg = " & calcBrg

DoCmd.Echo True

EndCode:
DoCmd.Echo True

On Error GoTo 0
Exit Function

Exit_calcBrg:
DoCmd.Echo True
Exit Function

calcBrg_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
calcBrg of Module basBearingAndRange"
Resume Exit_calcBrg


End Function


And here is the query i am using it in -

SELECT qryResources.ResourceSpecID, qryResources.BaseName,
qryResources.BaseLat, qryResources.BaseLong,
Format(calcRange(Forms!frmMainDetails!Lat,Forms!frmMainDetails!Long,[BaseLat],[BaseLong],"N"),"Fixed")
AS Range, qryResources.BaseActive,
Format(calcBrg([InstallationLat],[InstallationLong],[BaseLat],[BaseLong]),"000")
AS Brg, qryAllDetails.qryMainDetails.GroupID, qryAllDetails.Lat AS
InstallationLat, qryAllDetails.Long AS InstallationLong
FROM qryResources, qryAllDetails
WHERE
(((qryAllDetails.qryMainDetails.GroupID)=[Forms]![frmMainDetails]![InstallationID]))
ORDER BY
Format(calcRange(Forms!frmMainDetails!Lat,Forms!frmMainDetails!Long,[BaseLat],[BaseLong],"N"),"Fixed");


I am pulling my hair out now. All advice welcome. TIA

Kevin
Click to expand...
Click to expand...
Click to expand...
 
B

BusterB

Hi Karl,

Thanks for your suggestions on this. I have spent the last couple of days
trying all different ways round this. I filter the query using the criteria
so that it only returns the records I want position to do the calculation
on, then base another query on it and do the calcualtions. no joy.

When I use this Function in a query it just sends it into a never-ending
loop. I used debug.print to see is the value were changing but they don't.
There are only 13 records returned in qryResources and the calculations
return the same 13 calculated values which are correct. All I need to do is
find out why it keeps looping.

When I base my subform on my query without the calculations and do the
calculations on the form, the function works fine. This just makes me even
more puzzled.

Why doesn't it work in my query?

Thanks.


KARL DEWEY said:
Try a union query then the calculations.
--
Build a little, test a little.


BusterB said:
Hi Karl,

Thanks for your reply, but I cannot join the two queries. I want to have
the bearings and distances calculated between all records in both tables.

Then I was going to filter the records using qryAllDetails.GroupID to show
just one position from qryAllDetails and then show all the calculated
bearings and distances from qryResources.

Am I making it clear what I want. The two queries are not linked in any way
apart from they both contain positions in them. I want a query that takes the
position info in qryAllDetails and calculates the bearings and ranges to or
from the positions in qryResources.

Thanks.

KARL DEWEY said:
Maybe you need to join qryResources and qryAllDetails in your FROM statement
because you are creating a Cartesian effect.

--
Build a little, test a little.


:

Hi,

I have followed this newsgroup for years and have never needed to post a
question until now as someone has usually had the same question answered
before. But....

Below is a function I wrote that calculates the bearing between two
positions given in latitude and longitude (stored as minutes N and E for
positive numbers)

When I test the function in the immediate window it works fine.

When I include the function in a query field, it sends the query into a
never-ending loop of calculations.

I have a similar Function that calulates the Distance between the two
positions and it works fine.

What is wrong???

Here is my function -

Function calcBrg(sLat As Single, sLong As Single, fLat As Single, fLong As
Single) As Single

Dim dLat As Single 'difference in lat
Dim dLong As Single 'difference in long
Dim mLat As Single 'mean latitude
Dim xLat As Single
Dim aBrg As Single
Dim finalBrg As Single

On Error GoTo calcBrg_Error

DoCmd.Echo False

'co-ord stored as minutes, convert to deg before use
sLat = sLat / 60
sLong = sLong / 60
fLat = fLat / 60
fLong = fLong / 60

If sLat = 0 Then
'no position

Else

dLat = fLat - sLat 'difference in latitude

'If difference = 0 then make it negligable
If dLat = 0 Then
dLat = 0.000000001
End If

dLong = fLong - sLong 'difference in longitude
mLat = (sLat + fLat) / 2 'find mean latitude
mLat = mLat * (Pi / 180) 'convert mean lat to radians
xLat = Cos(mLat) * dLong
xLat = (xLat / dLat)
aBrg = rad2deg(Atn(xLat))


If dLat < 0 Then
finalBrg = aBrg + 180
ElseIf aBrg < 0 Then
finalBrg = aBrg + 360
Else
finalBrg = Nz(aBrg)
End If
End If

calcBrg = finalBrg
'Debug.Print "calcBrg = " & calcBrg

DoCmd.Echo True

EndCode:
DoCmd.Echo True

On Error GoTo 0
Exit Function

Exit_calcBrg:
DoCmd.Echo True
Exit Function

calcBrg_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
calcBrg of Module basBearingAndRange"
Resume Exit_calcBrg


End Function


And here is the query i am using it in -

SELECT qryResources.ResourceSpecID, qryResources.BaseName,
qryResources.BaseLat, qryResources.BaseLong,
Format(calcRange(Forms!frmMainDetails!Lat,Forms!frmMainDetails!Long,[BaseLat],[BaseLong],"N"),"Fixed")
AS Range, qryResources.BaseActive,
Format(calcBrg([InstallationLat],[InstallationLong],[BaseLat],[BaseLong]),"000")
AS Brg, qryAllDetails.qryMainDetails.GroupID, qryAllDetails.Lat AS
InstallationLat, qryAllDetails.Long AS InstallationLong
FROM qryResources, qryAllDetails
WHERE
(((qryAllDetails.qryMainDetails.GroupID)=[Forms]![frmMainDetails]![InstallationID]))
ORDER BY
Format(calcRange(Forms!frmMainDetails!Lat,Forms!frmMainDetails!Long,[BaseLat],[BaseLong],"N"),"Fixed");


I am pulling my hair out now. All advice welcome. TIA

Kevin
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Function in Query is "too complex" 3

Top