Calculating direction

[ACTHelp]

I have a database which contains within it the addresses of clients. I need to be able to calculate which clients are located west of a certain area. For instance I needed to calculate if they're located west of Washington Road? This particular city is not laid out in a good grid format where I could potentially use streets and avenues. The city has streets and avenues boulevards parkways you name it. No rhyme or reason to the layout so that method is out. Thanks for your help.

 

[HarleyQuinn]

Do you only have addresses to do this with?

The easier way would be to download a Zipcode (or postcode as I'm in the UK) file containing Coordinates for that identifier and create a table from it. Then you can query the coordinates against the coordinates of your desired location to see if it's west.

I can't easily see any other way of doing this.

Last postcode file my company got wasn't cheap though...

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[ACTHelp]

That's an interesting thought. The geographical area is rather small, so I could potentially create the zip code file myself. I'll look into it, thanks.

 

[HarleyQuinn]

Glad I could help

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[CaptainD]

In California, address numbers usually start low and work their way up from a dividing line. So 1000 W. Somestreet would be farther West then 500 W. Somestreet

If yours is similar you could go with streetNumber >= 501

North and South streets would cause a little more work.

If your database does not have the street number separate from the street name, thne you'll have additional work in extracting that part of the address.

Private Sub Command0_Click()
Dim streetName As String
Dim streetNumber As String
Dim Position As Integer

streetName = "1234 N. SomeStreet"
Position = InStr(1, streetName, " ")
streetNumber = Left(streetName, Position - 1)
MsgBox streetNumber
End Sub

 

[CaptainD]

Our local phone books (California) have maps with the zipcodes in the front of the book.

You would also have to link the zip to each address. If the zip expands across both sides of the river then you would be back to square one.

Do you have someone or someplace with access to a GIS program that could pull all the address from that area?

 

[ck1999]

You could look at geocoding. if this is omething that would not need updating often. google geocoding and then you can enter large qty of address at 1 time get the Long and lat for each address and then base calculation off this.

It maybe a little more work in the beginning.

ck1999

 

[ACTHelp]

You guys are fast and very good. Let me explain this a little bit more. I am actually disabled and on full-time disability. I was an IT Project manager for a large corporation and have done some Microsoft Access programming in the past. Parkinson's disease robbed me of my career and my ability to work full time. So now, just to get myself something to do, I do volunteer work for nonprofits. Yesterday, I talked with an organization that provides volunteer rides to people who need a ride to the doctor, the grocery store, etc. They recently discovered another organization provides the same type of transportation and some of the same areas. Hence they are going to quit providing that service in those areas. So I haven't actually seen this file yet but I know I'm going to be asked to do this. Currently the file is in Microsoft Excel. And I'm hoping they have zipcodes. They're only half a dozen or so zip codes for this particular county. Budget, of course, is zero. I'm, once again, a volunteer but they do have computers and they do have Microsoft Access and Excel. I’ll know next week exactly what the file looks like and what the parameters are for eliminating various clients.