copy part of one field to another

[Guest]

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

 

[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

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]

 

[Guest]

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

 

[John Vinson]

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

Ok... try this. If you have a Unit field and you want that to contain
"Suite A" for this address, use two Update queries (you could do it in
one but it's a lot simpler to handle Units and Suites separately).

Use a criterion on the address of

LIKE "*, Suite*"

to restrict the update to addresses containing a suite (as opposed to,
say, addresses on Suiteland Avenue).

Update the Unit field to

Mid([Address], InStr([Address], ", Suite") + 2)

InStr will find the position of the text string ", Suite"; the + 2
will skip over the comma and the blank, and update the Unit field to
"Suite A".

You can apply similar logic to Unit.

John W. Vinson[MVP]