Dealer Locator

[Galsaba]

Who can help me with a script to do "dealer locator" when i have zip codes of
each dealer, and the zip code of the customer.

Joe

 

[Ed Robichaud]

You don't give much info, but if I understand you correctly, one solution
would be to create a form (frmDealerFind) that has a record source of a
query (qryFindDealers) containing tblDealers (this would be dealer name,
address, etc.). That form should also contain a combo-box (cmbCustZip)
whose record source is tblCustomers (again, name/address/etc). Use the
combo-box to select a customer zipcode (or better yet have 2 combo-boxes to
select a start and end range of zipcodes). The select query that is the
record source for your form would then have the dealer zip criteria set to
"Forms!frmDealerFind!cmbCustZip". If you use a zip range, the criteria
would be ">=Forms!frmDealerFind!cmbCustZipStart and
<=Forms!frmDealerFind!cmbCustZipEnd". Add a command button to your run and
set its "On Click" event to: DoCmd.RunQuery "qryFindDealers". Now the user
will be able to select a customer zipcode, click the button and display a
list of matching dealers!
-Ed

 

[PC Datasheet]

Create a combobox on your form that has the value CustomerZipcode and displays
the CustomerZipcode.

Create a query based on your TblDealer and include the Dealer and DealerZipcode
fields. Put the following expression in the criteria of DealerZipcode:
Forms!NameOfYourForm!NameOfCombobox

 

[Galsaba]

I was trying to do it differntly.
I was trying to have a script that will find a dealer for me in a range of 30
miles.
So I will have a table of dealer, each dealer will have the ZIP, and its
longtitude and latitude. When the consumer enters his ZIP code, the software
wll check the lat and long and will try to find ZIPs of dealers in the radius
of 30 miles. this is what i need/

Aaron

 

[PC Datasheet]

What you are trying to do versus what you want to do is illogical. A zipcode
generally covers a very broad area and therefore the lat and long of points
inside a zipcode area are vastly different.

 

[Galsaba]

Sorry to see that you do not have solutions, or may be I did not describe what
I need. A few months ago I posted a similar questions, people answered, but I
cannot find the answer now.

You may see many applications like this on the web if you search under "find a
dealer near you". Here is one I found:
http://www.shakespeare-antennas.com/content/dealersearch.asp

But I do not want to have it on the web, I want to create it on access.
I will need at least two tables:
One is a table of each of our dealers, including their zip codes.
The other one is the entire zip database whick include all zip codes in the USA
and Canada, including the lat and long for each.

Then, when the user enters the zip code of the person who wants to find a
dealer, the application will find its lat and long from the second table. It
will then "open" an area of 30 miles radius from this lat and long, and will
find all the dealers' ZIPs in that area. Actulally,
the lat and long of each.

Then it will calculate the air distance from the user ZIP codes to each of the
Dealers ZIP codes.

Accurate? Not very much.
Illogical??

Answers will be appreciated.

Aaron

 

[Douglas J. Steele]

Sorry to see that you do not have solutions, or may be I did not describe what
I need. A few months ago I posted a similar questions, people answered, but I
cannot find the answer now.

If you already got an answer to this question, go to
http://groups.google.ca/advanced_group_search and find it again.

 

[Galsaba]

Thanks, I tried earlier. Could not find.

 

[Marc]

I was trying to have a script that will find a dealer for me in a range of 30
miles.
So I will have a table of dealer, each dealer will have the ZIP, and its
longtitude and latitude. When the consumer enters his ZIP code, the software
wll check the lat and long and will try to find ZIPs of dealers in the radius
of 30 miles. this is what i need/

No help with a script. But as pointed out zip codes cover a large area. What
I've seen used before is rather use map grid locations. Then if the person
wants someone in the 30 mile radius and you're grid is in 10 mile blocks,
you find locate the enquirer say 435 n / 456 w then you find all dealers
with locations 432 - 438n / 453 - 459 w. Or work straight from latitudes.
Yes, you'd have to licence a map display of some kind for the enquirer to
locate themselves, but you would probably have to do that anyway to show the
user where the dealers are located.
HTH
Marc