UtterAccess Discussion Forums > Property not found. (during query)

Full Version: Property not found. (during query)

UtterAccess Discussion Forums > Microsoft® Access > Access Queries

tamandt

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

CODE

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

WHERE (((GeoDistance(CSng([Longitude]),CSng([Latitude]),

  CSng((SELECT Longitude FROM Zip_codes WHERE Zipcode= Forms![Locate]![ZipTxt] )),

  CSng((SELECT Latitude FROM Zip_codes WHERE Zipcode= Forms![Locate]![ZipTxt] )),"mi"))

  <=CInt([Forms]![Locate]![DistanceTxt])));

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)

Any ideas?

Your help is appreciated.

Thnx

Edited to prevent page width expansion.

Edited by: VanThienDinh on Sun Dec 28 20:53:51 EST 2008.

GroverParkGeorge

Dec 28 2008, 09:10 PM

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.

George

tamandt

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.

CODE

   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
    Exit Function
End If

' 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

  End Function

Thanks again for your help in advance

tamandt

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

GroverParkGeorge

Dec 28 2008, 09:36 PM

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.

George

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.