Validation for partial addresses

[Gabe]

Hello,

I was wondering if anyone might know the code to use for validating partial
addresses? The addresses in my table should look like this: Salem, OR 97301.
However, some users are putting bad data in there like: sAlem or 9730. How
can I stop this from happening?

Thanks,
~Gabe

 

[KARL DEWEY]

You can create a lookup table and have user select with combo box.

 

[John W. Vinson]

Hello,

I was wondering if anyone might know the code to use for validating partial
addresses? The addresses in my table should look like this: Salem, OR 97301.
However, some users are putting bad data in there like: sAlem or 9730. How
can I stop this from happening?

Thanks,
~Gabe

You're trying to store three pieces of information (city, state,zip) in one
field. That's a violation of the very basic principle that fields should be
atomic. Consider instead having three fields, one for each distinct value.

Assuming that your scope is just the US and Canada, you can use a table of
Postcodes (6 character Text to accommodate US Zip codes and Canadian
postcodes); a table of states and provinces with a two-letter text primary key
and a text field for the state/province name; and a table of cities. The user
could then *SELECT* a properly spelled and capitalized value from each table
using a combo box. With a bit of code you can even have (say) the zip code
combo automatically fill in the state, and limit the city combo to the cities
(usually but not always just one) in that zipcode.

 

[John Spencer]

It kind of depends on how you define validate and where you intend to
use this.

IF Not (Address Like "?*, ?? #####") Then
'Address does not contain at least one character
'followed by a comma, a space, two characters, a space
'and five number characters.

ElseIf strComp(Left(Address,Instr(1,Address,",")), _
StrConv(Left(Address,Instr(1,Address,",")),3),0) <> 0 Then
'Address City Name may be incorrectly capitalized



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================