Property not found. (during query)
Dec 28 2008, 08:35 PM
I have this form that required two entries.... Zipcode and Distance.
A button is clicked and the following query is run
SELECT Mlist.[Date List Produced], Mlist.[COMPANY NAME], Mlist.ADDRESS,
Mlist.CITY, Mlist.STATE, Mlist.[XZIP CODE], Mlist.COUNTY,
Mlist.[LAST NAME], Mlist.[FIRST NAME], Mlist.[CONTACT TITLE], Mlist.[CONTACT GENDER],
Mlist.[CONTACT PROF TITLE], Mlist.BuySell, Mlist.YearOfGrad, Mlist.[LOCATION ADDRESS],
Mlist.[LOCATION ADDRESS CITY], Mlist.[LOCATION ADDRESS STATE], Mlist.ZIP,
Zip_codes.Latitude AS Latitude, Zip_codes.Longitude AS Longitude
FROM Mlist INNER JOIN Zip_codes ON Mlist.ZIP = Zip_codes.Zipcode
CSng((SELECT Longitude FROM Zip_codes WHERE Zipcode= Forms![Locate]![ZipTxt] )),
CSng((SELECT Latitude FROM Zip_codes WHERE Zipcode= Forms![Locate]![ZipTxt] )),"mi"))
Basically it is pulling a list of addresses within so many miles of a certain zip... I know the query is working because i had to debug the distance function to allow for 0 distance (comparing the same zipcodes). It went through many many calculations before failing due to comparing same zipcodes... which i then fixed.
Now i get "Property not found" (it does not specify any property...
If i immediately click the play button in VBA... i get this (shortened):
Home > Troubleshooting > This Expression is Typed Incorrectly, or it is too Complex to be Evaluated
This Expression is Typed Incorrectly, or it is too Complex to be Evaluated (error 3071)
Your help is appreciated.
Edited to prevent page width expansion.
Edited by: VanThienDinh on Sun Dec 28 20:53:51 EST 2008.
Dec 28 2008, 09:10 PM
From: Newcastle, WA
For trouble-shooting purposes, I would start by replacing the control references with hard-coded values of the appropriate data type to be sure the query returns expected values. Then, replace the hard-coded values, one at at time, with the corresponding contrl reference, testing as you go. If you find that one or more of the control references is not returning an appropriate value, then you need to look at that control on the form.
I would also look into the datatypes of the fields in the table and the controls on the form.
You are using the CSng() function to coerce the values to Single. I would normally expect ZIP codes to be formatted as text, not numbers. Without seeing how the function GeoDistance() works, of course, I can't tell whether that is an issue, but it certainly would be something to be looked into, given the error message returned.
Dec 28 2008, 09:25 PM
The CSng are used because it is pulling coordinates in longitutde and latitude (in decimal form)and finding the distances between them.
Somehow, when switching between sql view and other views Access added a crapload of parenthesis (sp)........
Once i fixed that the query runs for about 10 seconds (26,000 records) and then errors with INVALID USE OF NULL
The code that fails is part of the distance function: this line:
F = (CSng(Lat1) + CSng(Lat2)) / 2
It fails because Lat1 (latitude 1) is null.
I put a global counter into the routine which at crash displayes 42205.... yet there are only just over 26,000 records? None of them have blank logitudes or latitudes..... ????
If you need to see the code for distance, here it is.
Public Function GeoDistance(Long1, Lat1, Long2, Lat2, Units)
' Constant to convert degrees into radians
Const k As Double = 3.14159265358979 / 180
' Flattening of the earth
Const ff As Double = 1 / 298.257
Dim C As Double
Dim D As Double
Dim F As Double
Dim G As Double
Dim H1 As Double
Dim H2 As Double
Dim L As Double
Dim O As Double
Dim R As Double
Dim S As Double
Dim W1 As Double
Dim W2 As Double
Dim W3 As Double
Dim W4 As Double
Dim SG As Double
Dim CG As Double
Dim SF As Double
Dim CF As Double
Dim SL As Double
Dim CL As Double
Dim U As String
Dim UF As Double
U = LCase(Trim(Units))
UF = 1
If U = "mi" Then UF = 1.609344
If U = "nmi" Then UF = 1.852
counter = counter + 1
' Compute auxiliary angles
F = (CSng(Lat1) + CSng(Lat2)) / 2
G = (CSng(Lat1) - CSng(Lat2)) / 2
L = (CSng(Long1) - CSng(Long2)) / 2
'covers when comparing the same location
If G = 0 And L = 0 Then
GeoDistance = 0
' Compute sines and cosines of auxiliary angles
SG = Sin(G * k)
CG = Cos(G * k)
SF = Sin(F * k)
CF = Cos(F * k)
SL = Sin(L * k)
CL = Cos(L * k)
W1 = SG * CL: W1 = W1 * W1
W2 = CF * SL: W2 = W2 * W2
S = W1 + W2
W3 = CG * CL: W3 = W3 * W3
W4 = SF * SL: W4 = W4 * W4
C = W3 + W4
O = Atn(Sqr(S / C))
R = Sqr(S * C) / O
D = 2 * O * 6378.14
H1 = (3 * R - 1) / (2 * C)
H2 = (3 * R + 1) / (2 * S)
' Compute the angle between the points on a
' synthetic sphere connecting the two points.
W1 = SF * CG: W1 = W1 * W1 * H1 * ff + 1
W2 = CF * SG: W2 = W2 * W2 * H2 * ff
' Return the distance between the given locations in
' the units indicated by the units factor UF
GeoDistance = D * (W1 - W2) / UF
Thanks again for your help in advance (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)
Dec 28 2008, 09:32 PM
Nevermind, I apologize for the stupid questions... I guess ive just been working too long today....
There was not an empty record in the main table, but there was an empty table in the zip table..... figured it out.....
Thank you for your help though (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)
Dec 28 2008, 09:36 PM
From: Newcastle, WA
No problem. Sometimes it's just the process of explaining and following the steps that helps us spot the issue. Congratulations on solving your problem and continued success.
|Go to Top · Lo-Fi Version||Time is now: 21st May 2013 - 09:45 AM|