Take an Address table and create additional fields

  • Thread starter Thread starter Ray
  • Start date Start date
R

Ray

I have a table with a field in it called address. I need to seperate the
different components of the address into different fields and keep them in
the same table. For example if the address is 33 Lakeview Drive and is one
field I need for it to be in two fields. 33 in the first field and
Lakeview drive in the second field.

Thank you for your help
 
You will need to create an additional data field for the
second part, and edit your data accordingly. You might
try to do this in code or by a query but unfortunately
addresses are always unpredictable.

So e.g. instead of having Address you will have
Streetnumber
and
Streetname
etc. etc.

If you have already written a number of reports using the
field name Address you can always concatenate the various
parts in a query, e.g.

streetnumber & " " & streetname

that is, assuming everybody has a street number in a
street.


hth
 
If every address consisted of a number, followed by a space, followed by a
street name, it would be easy ...

UPDATE Table1 SET Table1.[Number] = Left$([Street],InStr([Street]," ")-1),
Table1.Street = Mid$([Street],InStr([Street]," ")+1);

Unfortunately, in the real world, addresses don't always fit into such neat
patterns, and you may need to modify this example to account for various
exceptions to the rule. Rather than modifying the original address field
('Street' in the example above) it may be safer to create two new fields,
one for the number and one for the new street address sans number, leaving
the original field alone for now. After you've made your changes and checked
the data, then you can delete the old address field and rename the new
street field with the old name.

As always when making bulk changes to data, it's wise to make a backup
first.
 
Thanks for your help. That did the trick.

Ray

Brendan Reynolds (MVP) said:
If every address consisted of a number, followed by a space, followed by a
street name, it would be easy ...

UPDATE Table1 SET Table1.[Number] = Left$([Street],InStr([Street]," ")-1),
Table1.Street = Mid$([Street],InStr([Street]," ")+1);

Unfortunately, in the real world, addresses don't always fit into such neat
patterns, and you may need to modify this example to account for various
exceptions to the rule. Rather than modifying the original address field
('Street' in the example above) it may be safer to create two new fields,
one for the number and one for the new street address sans number, leaving
the original field alone for now. After you've made your changes and checked
the data, then you can delete the old address field and rename the new
street field with the old name.

As always when making bulk changes to data, it's wise to make a backup
first.
Click to expand...
 
Back
Top