How to verify street address

[Anubix]

I'm doing an app for church. our food bank can only serve people from
specific zip codes. I have that part working fine. But there's one zip
code that lies in the borderline of our service area. The zip code is
cut in half by a main avenue. Anyone to the west of te avenue
qualifies for assistance. Everyone to the east doesn't. I know it's
kinda harsh to not serve people because they live across a road. But
that's another issue. In the meantime, how do I check that the
address enterd is to the east or west of the avenue? Do I have to
make a table of posisitve and negative street names? If so, how can I
obtain such a list?

Thanks, for your help.

 

[Ron Weiner]

Ask them when they register if they live east or west of the dividing
avenue. If they look hungry, feed them!

Ron W

 

[Linq Adams via AccessMonster.com]

I don't really see any practical way to do this! Ron's suggestion is probably
the best, assuming referrals are done in person or by phone, or check with a
city guide or MapQuest.

I suppose you could make a table of street names that fall within the zipcode
and are on the proper side of the main avenue, and then, in the AfterUpdate
for the street name, in your form, use DLookUp() with a criteria using
***Like*** to see
whether the street name is within the service area. But sometimes, manual
ways are still the best!

BTW, there's always going to be a dividing line somewhere! Once, in another
lifetime, I worked in a major trauma center, where we had a huge city map
mounted on the ER wall, with a big red line thru it. The reason? If we
transferred patients to a Veterans Hospital, it had to be to the right VA
Hospital, and their services areas split our city! If we sent them to the
wrong facility (each of which were 120 miles away) the ambulances had to turn
around and take them to the correct one! A 120 mile trip turned into a 360
mile trek!

 

[Anubix]

It's true that we don't just kick them out because they live across
the borderline, but we need to get a handle on it somehow. Otherwise,
the people in our direct area of service won't get anything, and we're
there to help them. That's why I said "that's another issue." I
didn't want to get in all the details of our mission but focus on the
technical aspects on how to get it done. It's growing so big that I
wanted to help the ladies signing people in. I'll probably just leave
it as a manual process.

Thanks anyway.

 

[Paul Shapiro]

If the number of lookups is small, manual is probably best. If the number is
high enough that it's worth the effort for automation, look up geocoding
solutions. With geocoding you can define your boundaries, pass an address
and determine if that address is inside your boundaries. Geocoding takes
some development work and/or money.

If the number of streets is small enough you could include the qualifying
streets in a table with the min and max street number that qualifies. You
still have possible problems if the user mis-spells the street name, but if
it's not too many streets maybe a drop-down list will mostly solve that.

It's true that we don't just kick them out because they live across
the borderline, but we need to get a handle on it somehow. Otherwise,
the people in our direct area of service won't get anything, and we're
there to help them. That's why I said "that's another issue." I
didn't want to get in all the details of our mission but focus on the
technical aspects on how to get it done. It's growing so big that I
wanted to help the ladies signing people in. I'll probably just leave
it as a manual process.

Thanks anyway.

 

[Evi]

I think you do need a database for this otherwise your volunteers will waste
loads of time asking each other if nos 1 to 11 of x street qualifies. You
could still use your own discretion if you want to make an exception for
individuals but the facts are there if you need them.

The design could be something like this

TblQualify
QualID
QualNo (a number field containing the numbers 1 to 4)
Qualif (a text field) with

All this qualifies,
Part of this qualifies,
None this qualifies,
Not known if this qualifies


TblZipCode contains all the zipcodes you come across
ZipQualID is a foreign key linked from QualID field TblQualify Hopefully you
should be able to fill this in immediately.


TblStreet contains all the street names with a ForeignKey link to
TblZipCode.

TblStreetSection
contains the Foreign Key link to TblStreet's Primary key and all streets are
appended to it using an Append query .

Streets whose ZipQualID will be given QualID 1 when you do the append.
A street which partly qualifies can be added more than once to this table


The next field, QualID is also linked to TblQualify
(be a bit careful when using this that you don't have a non-updatable
recordset - in queries you will need to add TblQualify twice to the grid,
give the second copy a different name eg QryZipQual, QryStreetSecQual, and
link each copy to a different QualID)



the next field, QualSect, contains the section which is qualified eg
WestSide, numbers 1 to 11, All (or leave blank)


Evi