Well it actually does not seem as complicated after I took a peek at it.
Assuming you have a table structure like the following
Advertisements
AdID - Autonumber - PK
AdName - Text
PostalCode - Text
PostalCodes
PostalCode - Text(5) - PK
Lat - Number(Double)
Lon - Number(Double)
Then you could simple do a query as the following:
SELECT Advertisements.AdID, Advertisements.AdName, Advertisements.PostalCode
FROM Advertisements INNER JOIN PostalCodes ON Advertisements.PostalCode = PostalCodes.PostalCode
WHERE SQR
(
(
(69.1 * (PostalCodes.Lat - (Select [PostalCodes].[Lon] From PostalCodes where PostalCodes.PostalCode=[PostCode])))
*
(69.1 * (PostalCodes.Lat - (Select [PostalCodes].[Lon] From PostalCodes where PostalCodes.PostalCode=[PostCode])))
)
+
(
(69.1 * (PostalCodes.Lon - (Select [PostalCodes].[Lat] From PostalCodes where PostalCodes.PostalCode=[PostCode])) * cos((Select [PostalCodes].[Lon] From PostalCodes where PostalCodes.PostalCode=[PostCode]) / 57.3))
* ;
(69.1 * (PostalCodes.Lon - (Select [PostalCodes].[Lat] From PostalCodes where PostalCodes.PostalCode=[PostCode])) * cos((Select [PostalCodes].[Lon] From PostalCodes where PostalCodes.PostalCode=[PostCode]) / 57.3))
)
)
< 50;
|
and it theoretically should show all Ads within 50 miles. Just substitude [PostCode] in asp with something like: ...'" & strUserPostCode & "'...."
I am sure you need at least access 2000 for that but that should be a given.
Edited by michael
Topic Options
Post Options
Thanks(0)
