Search/find postcodes from table within inputted postcode & radius

[Widge]

s there such a plug in or piece of code that can handle the
following scenario?

1) I have a list of suppliers, goods they sell and addresses/postcodes
2) Customer comes along, says they need item X but must be from a
supplier within 7 miles of their location
3) I pump in their postcode and I get the result being the location of
all relevant suppliers within that radius who are able to stock item X*

*Item X will always have the same code between suppliers (we will be designating it an ID).

I have heard of someone who has something that can find the location
of suppliers within a radius set up in an Excel spreadsheet, but they
WON'T share it! So throwing myself on you guys for assistance. If it can be Excel-ised, I'm sure it can be Access-ed!

Trawled the net to no real avail. So relying on the experience of
others. Any info would be great really!

Thanks

Si

[maxamis4]

The closest thing I have ever heard that can do this for you is a streets and trip reference library that you can add to access. I am not sure how familiar you are with code but if you purchase streets and trips and add the library reference to access, it will allow you to access the streets and trips mapping library. who ever has a spread sheet basically created a distance address library, similiar to map quest, which uses arrays to determine total distance or a radius.

other than that I would search google for distance measurement databases.

[Widge]

I did some conferring with a mate and he came up with this based on your advice. Needed:

1) ukpostcodes list tab (you'll have to find UK postcodes)
2) my suppliers list (with postcodes in column B)
3) tab called InsertCustomerPostcode

You then stick a formula in the suppliers list at the end of your data:

=SQRT(((ABS(VLOOKUP(TRIM(LEFT(InsertCustomerPostcode!$B$3,LEN(InsertCustomerPostcode!$B$3)-3)),'uk-postcodes'!$A$2:$C$2822,2, FALSE) -VLOOKUP(TRIM(LEFT(Suppliers!B2,LEN(Suppliers!B2)-3)),'uk-postcodes'!$A$2:$C$2822,2,FALSE)))^2)+((ABS(VLOOKUP(TRIM(LEFT(InsertCustomerPostcode!$B$3,LEN(InsertCustomerPostcode!$B$3)-3)),'uk-postcodes'!$A$2:$C$2822,3,FALSE)-VLOOKUP(TRIM(LEFT(Suppliers!B2,LEN(Suppliers!B2)-3)),'uk-postcodes'!$A$2:$C$2822,3, FALSE)))^2))/1609

all you need to do then is stick in a postcode in InsertCustomerPostcode B3 and then the lookups do their work.

Of course, now I need to find a way of getting this in Access and then restricting to x radius.

[Widge]

I now have this in a spreadsheet driven by a Userform. Is there any way of getting Access to interact with this at all?

Tried to think of a way to do what Excel is doing by using a query and Dlookups, but the complexity of it all caused a minor brain explosion.

Not entirely sure if what I was doing was possible. I was basically trying to get an amend query to perform the above calculation except with Access syntax like Dlookups and calculate distances when you input values into a form. You would have a textbox for inputting the postcode into, the query then uses the first part of the postcode to dlookup through the list of ukpostcodes, pulling off the location values etc... all what the above vlookups were doing basically. But so complicated to knock up in expression builder that I just couldn't get it to work.

So looking to see if I can find an easier way of doing things. Utilising something I know is working with my database (since I want to have everything running from one central point).

If you REALLY want to have a mess around with this, I can upload the UK Postcode file that I have and can give you a bit more of an indepth runthrough of what my setup is.

[sm8082]

Hi, do you this program written? and along with uk post code database?

[Stewart Ross]

Hi sm8082. I doubt you will get an answer from an old thread (dating as it does from 2007). In any event, in the UK Royal Mail does not supply postcode databases for free. Although there are some royalty-free alternatives which try to provide similar data, including eastings and northings gathered on the ground by volunteers, there is simply no easy alternative to the data supplied commercially by Royal Mail (or by Ordnance Survey, which also uses Royal Mail data for the postcode and household information).

Unless posters use publically-available sources they would be in breach of copyright rules to publish UK postcode data sourced from Royal Mail or Ordnance Survey sources. We could not condone or assist in such illegal activities.

-Stewart