Link to home
Start Free TrialLog in
Avatar of JP_TechGroup
JP_TechGroupFlag for United States of America

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?

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));

Open in new window

Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

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
Avatar of JP_TechGroup

ASKER

Here is the function:
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

Open in new window


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
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)
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.