JP_TechGroup
asked on
Data Type Mismatch in Criteria - Access
I have a query which includes a function to derive a column. The function is set to return a data type of double.
When I attempt to assign a criteria to this column of less than 1, I receive the above error. We need to be able to set the criteria to .25, moreover this actually used to work, but has suddenly started producing the error. Query example is below:
The function looks at a latitude and longitude value and compares it to the values stored in a table and returns the distance between the two points, rounded to the nearest 100th. Any thoughts?
When I attempt to assign a criteria to this column of less than 1, I receive the above error. We need to be able to set the criteria to .25, moreover this actually used to work, but has suddenly started producing the error. Query example is below:
The function looks at a latitude and longitude value and compares it to the values stored in a table and returns the distance between the two points, rounded to the nearest 100th. Any thoughts?
SELECT TOP 100 tbl_orders.id_order,
GetMiles(29.7716857,-95.3735693,[tbl_orders].[address_lat],[tbl_orders].[address_long])
FROM tbl_orders
WHERE (((GetMiles(29.7716857,-95.3735693,[tbl_orders].[address_lat],[tbl_orders].[address_long]))<=0.25));
Without the source code for that GetMiles function and some sample data that replicates the problem, it will be difficult to know where to start
ASKER
Here is the function:
And here is a result set from the query:
id_order oDistance
238 0.8
502 0.98
617 0.98
681 0.98
712 0.98
773 0.98
777 0.98
896 0.98
914 0.98
1248 0.98
1458 0.91
1491 0.98
1520 0.98
1605 0.76
1697 0.74
1757 0.9
1779 0.98
1971 0.98
2132 0.98
2305 0.98
2359 0.8
2368 0.98
2425 0.98
2748 0.98
2845 0.98
2858 0.98
2866 0.87
2990 0.98
3063 0.98
3323 0.89
3716 0.98
3717 0.98
3872 0.98
3929 0.98
4244 0.98
4328 0.98
4392 0.98
4537 0.82
4582 0.98
4608 0.87
4763 0.98
4785 0.98
4836 0.98
4850 0.98
5104 0.75
5145 0.98
5210 0.98
5293 0.98
5460 0.75
5501 0.98
5576 0.98
5691 0.53
5749 0.98
5750 0.98
5776 0.79
5993 0.98
6049 0.94
6161 0.81
6452 0.98
6575 0.27
6580 0.98
6680 0.65
6700 0.98
6904 0.12
7022 0.33
7281 0.88
7425 0.81
7426 0.81
7879 0.98
8119 0.58
8255 0.98
8317 0.92
8320 0.98
8617 0.98
8672 0.34
9832 0.88
10029 0.82
10325 0.08
10534 0.92
10799 0.44
10925 0.75
11135 0.91
11136 0.91
11288 0.91
11289 0.91
12732 0.7
13294 0.92
13593 0.68
13883 0.96
13884 0.96
13885 0.96
13950 0.79
13951 0.79
13952 0.79
16269 0.78
16329 0.79
16337 0.25
16338 0.25
16687 0.27
16692 0.27
Public Function GetMiles(lat1Degrees As Double, lon1Degrees As Double, lat2Degrees As Double, lon2Degrees As Double) As Double
Dim earthSphereRadiusKilometers As Double
Dim kilometerConversionToMilesFactor As Double
Dim lat1Radians As Double
Dim lon1Radians As Double
Dim lat2Radians As Double
Dim lon2Radians As Double
Dim AsinBase As Double
Dim DerivedAsin As Double
'Mean radius of the earth (replace with 3443.89849 to get nautical miles)
earthSphereRadiusKilometers = 6371
'Convert kilometers into miles (replace 0.621371 with 1 to keep in kilometers)
kilometerConversionToMilesFactor = 0.621371
'Convert each decimal degree to radians
lat1Radians = (lat1Degrees / 180) * 3.14159265359
lon1Radians = (lon1Degrees / 180) * 3.14159265359
lat2Radians = (lat2Degrees / 180) * 3.14159265359
lon2Radians = (lon2Degrees / 180) * 3.14159265359
AsinBase = Sin(Sqr(Sin((lat1Radians - lat2Radians) / 2) ^ 2 + Cos(lat1Radians) * Cos(lat2Radians) * Sin((lon1Radians - lon2Radians) / 2) ^ 2))
DerivedAsin = (AsinBase / Sqr(-AsinBase * AsinBase + 1))
'Get distance from [lat1,lon1] to [lat2,lon2]
GetMiles = Round(2 * DerivedAsin * (earthSphereRadiusKilometers * kilometerConversionToMilesFactor), 2)
End Function
And here is a result set from the query:
id_order oDistance
238 0.8
502 0.98
617 0.98
681 0.98
712 0.98
773 0.98
777 0.98
896 0.98
914 0.98
1248 0.98
1458 0.91
1491 0.98
1520 0.98
1605 0.76
1697 0.74
1757 0.9
1779 0.98
1971 0.98
2132 0.98
2305 0.98
2359 0.8
2368 0.98
2425 0.98
2748 0.98
2845 0.98
2858 0.98
2866 0.87
2990 0.98
3063 0.98
3323 0.89
3716 0.98
3717 0.98
3872 0.98
3929 0.98
4244 0.98
4328 0.98
4392 0.98
4537 0.82
4582 0.98
4608 0.87
4763 0.98
4785 0.98
4836 0.98
4850 0.98
5104 0.75
5145 0.98
5210 0.98
5293 0.98
5460 0.75
5501 0.98
5576 0.98
5691 0.53
5749 0.98
5750 0.98
5776 0.79
5993 0.98
6049 0.94
6161 0.81
6452 0.98
6575 0.27
6580 0.98
6680 0.65
6700 0.98
6904 0.12
7022 0.33
7281 0.88
7425 0.81
7426 0.81
7879 0.98
8119 0.58
8255 0.98
8317 0.92
8320 0.98
8617 0.98
8672 0.34
9832 0.88
10029 0.82
10325 0.08
10534 0.92
10799 0.44
10925 0.75
11135 0.91
11136 0.91
11288 0.91
11289 0.91
12732 0.7
13294 0.92
13593 0.68
13883 0.96
13884 0.96
13885 0.96
13950 0.79
13951 0.79
13952 0.79
16269 0.78
16329 0.79
16337 0.25
16338 0.25
16687 0.27
16692 0.27
Does it work if you remove the criteria completely, or does it still return an error?
I don't see an error handler in your GetMiles function. I would add an error handler in that function and pass back a negative value if an error is encountered in the function.
Dale
I don't see an error handler in your GetMiles function. I would add an error handler in that function and pass back a negative value if an error is encountered in the function.
Dale
ASKER
It is there, I pulled it out before posting. The function is not returning an error, the error occurs when I open the recordset.
The query works with no criteria or a value equal or greater than 1, including a decimal value (1.25 for example)
The query works with no criteria or a value equal or greater than 1, including a decimal value (1.25 for example)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Found the issue. In the order table, a single record was missing a latitude entry and the error handling didn't catch it.
Updated the query to insert a 0 value for any null and viola'. Thanks all.
Updated the query to insert a 0 value for any null and viola'. Thanks all.