copy part of one field to another

A

Amanda123

Hi,

Within my database, I have an 'Address' field. Some of the addresses within
this field include unit/suite numbers. For all addresses which do, I would
like to remove only the unit/suite numbers and copy them to a separate field.
How should I structure my update query in order to do this?

Thanks
 
J

John Vinson

Hi,

Within my database, I have an 'Address' field. Some of the addresses within
this field include unit/suite numbers. For all addresses which do, I would
like to remove only the unit/suite numbers and copy them to a separate field.
How should I structure my update query in order to do this?

Thanks
Click to expand...

Ideally, you should NOT store this information redundantly. Instead,
it's a lot easier to store the Unit as a separate field to start with,
and concatenate the values as needed.

If you were to have a list of addresses, how can you - reliably -
identify which addresses contain a unit/suite, and how can you
identify what portion of the address is the unit? e.g.

3165 31st St
3255 31st A

Why is the second one Unit A, and the first isn't unit St?

In short - this can be very difficult to do automatically, unless your
address fields are much better behaved than is typical!

John W. Vinson[MVP]
 
A

Amanda123

Hi again,

The suite/unit numbers within the addresses all start with the word 'suite'
or 'unit' (i.e. - 123 Somewhere Street, Suite A). So those addresses with
suite/unit can be identified. I just don't know how to automatically 'cut and
paste' the suite/unit part of each address into a separate field.

Thanks
 
Top