Parameter Query Error - Expression is too complex to be evaluated

[Nancy]

I have a simple query which uses a function to return a numeric value
representing distance between to locations.

The user is prompted to enter the Starting Latitude and Longitude
coordinates and the function compares those to a search table a returns the
distance in miles. Problem is, it returns a value for all records in my
search table.

I want to limit the number of records returned by a specific number of miles
(ie: all records less than 75 miles from the Starting Lat/Lon). But when I
enter a value in the Access criteria such as <75, I recieve the error "This
expression...is too complex to be evaluated.

Here is my SQL statement:
SELECT COMPANY2.D_CODE, COMPANY2.[COMPANY NAME], COMPANY2.CITY,
COMPANY2.[STATE/PROV], COMPANY2.[ZIP/P_CODE], COMPANY2.FirstOfCOUNTY_DESC AS
COUNTY, tblCompanyLatLon_ALL.LATITUDE, tblCompanyLatLon_ALL.LONGITUDE,
Val(DistCalc([Enter Starting Latitude],[Enter Starting
Longitude]*-1,[LATITUDE],[LONGITUDE],"M")) AS [Distance from Center
FROM COMPANY2 LEFT JOIN tblCompanyLatLon_ALL ON COMPANY2.D_CODE =
tblCompanyLatLon_ALL.D_CODE
WHERE (((tblCompanyLatLon_ALL.LATITUDE) Is Not Null))
ORDER BY Val(DistCalc([Enter Starting Latitude],[Enter Starting
Longitude]*-1,[LATITUDE],[LONGITUDE],"M"));

Any help would be appreciated.
Thanks, Nancy

 

[Jerry Whittle]

Looks like [Distance from Center is missing a closing ].

What does the DistCalc function do?

 

[Jerry Whittle]

Take it back about [Distance from Center . It probably should read:

AS "Distance from Center"

 

[Nancy]

It had the closing bracket...just lost it during copy/paste. The function
returns the distance (ie: 20.72898) and is working just fine until I try to
limit the query on that column. For some reason, Access doesn't recognize
this as a numeric, I guess?

Here's the function:
Public Function DistCalc(Lat1 As Single, Lon1 As Single, Lat2 As Single,
Lon2 As Single, UnitFlag As String)
Dim LatRad1 As Single
Dim LonRad1 As Single
Dim LatRad2 As Single
Dim LonRad2 As Single
Dim LonRadDif As Single
Dim RadDist
Dim X As Single
Dim PI As Single
Dim DistKM As Single
Dim DistMI As Single
PI = 3.141592654
If IsNull(Lat1) Then
Exit Function
End If
If Lat1 = 0 Or Lon1 = 0 Or Lat2 = 0 Or Lon2 = 0 Then
DistCalc = Null
Exit Function
ElseIf Lat1 = Lat2 And Lon1 = Lon2 Then
DistCalc = 0
Exit Function
End If
LatRad1 = Lat1 * PI / 180
LonRad1 = Lon1 * PI / 180
LatRad2 = Lat2 * PI / 180
LonRad2 = Lon2 * PI / 180
LonRadDif = Abs(LonRad1 - LonRad2)
X = Sin(LatRad1) * Sin(LatRad2) + Cos(LatRad1) * Cos(LatRad2) *
Cos(LonRadDif)
If Sqr(-X * X + 1) = 0 Then
RadDist = 0
Else
RadDist = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)
End If
DistMI = RadDist * 3958.754
DistKM = DistMI * 1.609344
DistCalc = IIf(UnitFlag = "M", DistMI, DistKM)

End Function

Take it back about [Distance from Center . It probably should read:

AS "Distance from Center"

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a simple query which uses a function to return a numeric value
representing distance between to locations.

The user is prompted to enter the Starting Latitude and Longitude
coordinates and the function compares those to a search table a returns the
distance in miles. Problem is, it returns a value for all records in my
search table.

I want to limit the number of records returned by a specific number of miles
(ie: all records less than 75 miles from the Starting Lat/Lon). But when I
enter a value in the Access criteria such as <75, I recieve the error "This
expression...is too complex to be evaluated.

Here is my SQL statement:
SELECT COMPANY2.D_CODE, COMPANY2.[COMPANY NAME], COMPANY2.CITY,
COMPANY2.[STATE/PROV], COMPANY2.[ZIP/P_CODE], COMPANY2.FirstOfCOUNTY_DESC AS
COUNTY, tblCompanyLatLon_ALL.LATITUDE, tblCompanyLatLon_ALL.LONGITUDE,
Val(DistCalc([Enter Starting Latitude],[Enter Starting
Longitude]*-1,[LATITUDE],[LONGITUDE],"M")) AS [Distance from Center
FROM COMPANY2 LEFT JOIN tblCompanyLatLon_ALL ON COMPANY2.D_CODE =
tblCompanyLatLon_ALL.D_CODE
WHERE (((tblCompanyLatLon_ALL.LATITUDE) Is Not Null))
ORDER BY Val(DistCalc([Enter Starting Latitude],[Enter Starting
Longitude]*-1,[LATITUDE],[LONGITUDE],"M"));

Any help would be appreciated.
Thanks, Nancy

 

[Jerry Whittle]

I think that I'm getting a clue. Are you entering in <75 at one of the
parameter prompts OR do you have a slightly different query wher <75 is part
of the WHERE Clause?

If entering <75 at a parameter prompt, that won't work. You'll need to do
something like putting the less than sign < before the [ in the parameter
within the query itself.

If putting it in the WHERE clause, you may need to make the current SQL
statement a nested or subquery to return all the records then limit it in the
main query's WHERE clause. In other words the SQL statement that you listed
here would become the FROM when wrapped with ( ) .
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

It had the closing bracket...just lost it during copy/paste. The function
returns the distance (ie: 20.72898) and is working just fine until I try to
limit the query on that column. For some reason, Access doesn't recognize
this as a numeric, I guess?

Here's the function:
Public Function DistCalc(Lat1 As Single, Lon1 As Single, Lat2 As Single,
Lon2 As Single, UnitFlag As String)
Dim LatRad1 As Single
Dim LonRad1 As Single
Dim LatRad2 As Single
Dim LonRad2 As Single
Dim LonRadDif As Single
Dim RadDist
Dim X As Single
Dim PI As Single
Dim DistKM As Single
Dim DistMI As Single
PI = 3.141592654
If IsNull(Lat1) Then
Exit Function
End If
If Lat1 = 0 Or Lon1 = 0 Or Lat2 = 0 Or Lon2 = 0 Then
DistCalc = Null
Exit Function
ElseIf Lat1 = Lat2 And Lon1 = Lon2 Then
DistCalc = 0
Exit Function
End If
LatRad1 = Lat1 * PI / 180
LonRad1 = Lon1 * PI / 180
LatRad2 = Lat2 * PI / 180
LonRad2 = Lon2 * PI / 180
LonRadDif = Abs(LonRad1 - LonRad2)
X = Sin(LatRad1) * Sin(LatRad2) + Cos(LatRad1) * Cos(LatRad2) *
Cos(LonRadDif)
If Sqr(-X * X + 1) = 0 Then
RadDist = 0
Else
RadDist = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)
End If
DistMI = RadDist * 3958.754
DistKM = DistMI * 1.609344
DistCalc = IIf(UnitFlag = "M", DistMI, DistKM)

End Function

Take it back about [Distance from Center . It probably should read:

AS "Distance from Center"

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a simple query which uses a function to return a numeric value
representing distance between to locations.

The user is prompted to enter the Starting Latitude and Longitude
coordinates and the function compares those to a search table a returns the
distance in miles. Problem is, it returns a value for all records in my
search table.

I want to limit the number of records returned by a specific number of miles
(ie: all records less than 75 miles from the Starting Lat/Lon). But when I
enter a value in the Access criteria such as <75, I recieve the error "This
expression...is too complex to be evaluated.

Here is my SQL statement:
SELECT COMPANY2.D_CODE, COMPANY2.[COMPANY NAME], COMPANY2.CITY,
COMPANY2.[STATE/PROV], COMPANY2.[ZIP/P_CODE], COMPANY2.FirstOfCOUNTY_DESC AS
COUNTY, tblCompanyLatLon_ALL.LATITUDE, tblCompanyLatLon_ALL.LONGITUDE,
Val(DistCalc([Enter Starting Latitude],[Enter Starting
Longitude]*-1,[LATITUDE],[LONGITUDE],"M")) AS [Distance from Center
FROM COMPANY2 LEFT JOIN tblCompanyLatLon_ALL ON COMPANY2.D_CODE =
tblCompanyLatLon_ALL.D_CODE
WHERE (((tblCompanyLatLon_ALL.LATITUDE) Is Not Null))
ORDER BY Val(DistCalc([Enter Starting Latitude],[Enter Starting
Longitude]*-1,[LATITUDE],[LONGITUDE],"M"));

Any help would be appreciated.
Thanks, Nancy

 

[Nancy]

Yes, I'm trying to evaluate the returned value in the Where clause (see
below) and no matter how I try, I continue to get this message. The only
workaround I've found is to output the results to a table and then run
another query on that table. I don't like that but I may have to live with
it.

WHERE (((tblCompanyLatLon_ALL.LATITUDE) Is Not Null) AND
((Val(DistCalc([Enter Starting Latitude],[Enter Starting
Longitude]*-1,[LATITUDE],[LONGITUDE],"M")))<75))

I think that I'm getting a clue. Are you entering in <75 at one of the
parameter prompts OR do you have a slightly different query wher <75 is part
of the WHERE Clause?

If entering <75 at a parameter prompt, that won't work. You'll need to do
something like putting the less than sign < before the [ in the parameter
within the query itself.

If putting it in the WHERE clause, you may need to make the current SQL
statement a nested or subquery to return all the records then limit it in the
main query's WHERE clause. In other words the SQL statement that you listed
here would become the FROM when wrapped with ( ) .
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

It had the closing bracket...just lost it during copy/paste. The function
returns the distance (ie: 20.72898) and is working just fine until I try to
limit the query on that column. For some reason, Access doesn't recognize
this as a numeric, I guess?

Here's the function:
Public Function DistCalc(Lat1 As Single, Lon1 As Single, Lat2 As Single,
Lon2 As Single, UnitFlag As String)
Dim LatRad1 As Single
Dim LonRad1 As Single
Dim LatRad2 As Single
Dim LonRad2 As Single
Dim LonRadDif As Single
Dim RadDist
Dim X As Single
Dim PI As Single
Dim DistKM As Single
Dim DistMI As Single
PI = 3.141592654
If IsNull(Lat1) Then
Exit Function
End If
If Lat1 = 0 Or Lon1 = 0 Or Lat2 = 0 Or Lon2 = 0 Then
DistCalc = Null
Exit Function
ElseIf Lat1 = Lat2 And Lon1 = Lon2 Then
DistCalc = 0
Exit Function
End If
LatRad1 = Lat1 * PI / 180
LonRad1 = Lon1 * PI / 180
LatRad2 = Lat2 * PI / 180
LonRad2 = Lon2 * PI / 180
LonRadDif = Abs(LonRad1 - LonRad2)
X = Sin(LatRad1) * Sin(LatRad2) + Cos(LatRad1) * Cos(LatRad2) *
Cos(LonRadDif)
If Sqr(-X * X + 1) = 0 Then
RadDist = 0
Else
RadDist = Atn(-X / Sqr(-X * X + 1)) + 2 * Atn(1)
End If
DistMI = RadDist * 3958.754
DistKM = DistMI * 1.609344
DistCalc = IIf(UnitFlag = "M", DistMI, DistKM)

End Function

Take it back about [Distance from Center . It probably should read:

AS "Distance from Center"

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I have a simple query which uses a function to return a numeric value
representing distance between to locations.

The user is prompted to enter the Starting Latitude and Longitude
coordinates and the function compares those to a search table a returns the
distance in miles. Problem is, it returns a value for all records in my
search table.

I want to limit the number of records returned by a specific number of miles
(ie: all records less than 75 miles from the Starting Lat/Lon). But when I
enter a value in the Access criteria such as <75, I recieve the error "This
expression...is too complex to be evaluated.

Here is my SQL statement:
SELECT COMPANY2.D_CODE, COMPANY2.[COMPANY NAME], COMPANY2.CITY,
COMPANY2.[STATE/PROV], COMPANY2.[ZIP/P_CODE], COMPANY2.FirstOfCOUNTY_DESC AS
COUNTY, tblCompanyLatLon_ALL.LATITUDE, tblCompanyLatLon_ALL.LONGITUDE,
Val(DistCalc([Enter Starting Latitude],[Enter Starting
Longitude]*-1,[LATITUDE],[LONGITUDE],"M")) AS [Distance from Center
FROM COMPANY2 LEFT JOIN tblCompanyLatLon_ALL ON COMPANY2.D_CODE =
tblCompanyLatLon_ALL.D_CODE
WHERE (((tblCompanyLatLon_ALL.LATITUDE) Is Not Null))
ORDER BY Val(DistCalc([Enter Starting Latitude],[Enter Starting
Longitude]*-1,[LATITUDE],[LONGITUDE],"M"));

Any help would be appreciated.
Thanks, Nancy