format textbox addresses only takes st., dr., ave., pl. or ln.

[witchwater]

How do I format a textbox in Access to only take addresses in formats -
streets only St., Place only Pl., Avenue only Ave., etc. ? If I am suppose
to use the expression builder, can I see what is suppose to be in there?
Nothing I did works.

 

[Rick B]

Is this one field for the type of street, or are they putting in the whole
street address?

If they are entering the whole address (1234 Main Street East, Apt. 12) then
it would be very very difficult to do that. You'd have to create a table to
maintain the preferred abbreviations and all the possible ways they could be
entered wrong, then construct some type of code or update query to correct
them. I'd think this would be an extremely difficult task.

If it is a field all by itself, then use the combo-box wizard to help you
construct a list of possible entries and limit the user to selecting one of
the items from the list.

 

[Bruce Rusk]

If it's going to be for US addresses, you should use the official
abbreviations at
http://www.usps.com/ncsc/lookups/abbr_suffix.txt

 

[Vincent Johns]

If it's going to be for US addresses, you should use the official
abbreviations at
http://www.usps.com/ncsc/lookups/abbr_suffix.txt

Since the USPS prefers mailing labels to be in ALL CAPITAL letters, and
without periods after abbreviations (e.g., instead of "Ave." use "AVE"),
you might want to maintain 2 sets of abbreviations, one for mailing
labels, and one for inside addresses on letters. You might be able to
compute one version from the other, or you can use an Access Table with
both.

You might find it easiest to break addresses into subfields such as
[Building Number], [Direction], [Street Name], [Street Type], [Suite],
[Apartment], etc.

So "5832 NE Microsoft Bayou, Suite 666" could be stored as a set of
fields that could be checked for consistency and printed as either mixed
case or all upper case. Splitting up addresses into this format would
take some effort, and some addresses might not fit well. For addresses
that don't fit the usual pattern, you might use an [AddressNonstandard]
field, to be used if [Street Name] has a Null value. But once the
addresses are split, it would be easy to recombine them via an
expression like

UCase$(IIf(IsNull([Building Number]), "", [Building Number] & " ")
& IIf(IsNull([Direction]), "", [Direction] & " ")
& ...


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.