Separating data.

[Mo Man]

I am trying to write an update query that will separated the street
address and the apartment number from the field [PROPERTY LOCATION]

Currently in the [PROPERTY LOCATION] is an address that looks this

SMITH RD C0001

I would like to update [UNIT] with C0001 and REPLACE [PROPERTY
LOCATION] with SMITH RD

I have tried using the MID, RIGHT, LEFT and InSTR without any luck.

The apartments all starts with C0 and is 5 characters long, plus not
every [PROPERTY LOCATION] has a UNIT. So, I need to search for C0 and
then remove the C0 plus 3 addition characters.

I was hoping someone can shed some light on this.

Many thanks

 

[Jeff L]

You will need a combination of Left, Instr, and Right. Here's what I
did:
UPDATE Table1
SET Table1.[Property Location] = Trim(Left([Property
Location],InStr([Property Location],"C0")-1)), Table1.Unit =
Right([Property Location],5)
WHERE ((Right([Property Location],5) Like "C0*"));

Hope that helps!

 

[John Vinson]

I am trying to write an update query that will separated the street
address and the apartment number from the field [PROPERTY LOCATION]

Currently in the [PROPERTY LOCATION] is an address that looks this

SMITH RD C0001

I would like to update [UNIT] with C0001 and REPLACE [PROPERTY
LOCATION] with SMITH RD

I have tried using the MID, RIGHT, LEFT and InSTR without any luck.

The apartments all starts with C0 and is 5 characters long, plus not
every [PROPERTY LOCATION] has a UNIT. So, I need to search for C0 and
then remove the C0 plus 3 addition characters.

I was hoping someone can shed some light on this.

Many thanks

In the hope that you don't have any other occurances of "C0" in the
[PROPERTY LOCATION] field, and that the location field always ends
with the C0xxx value if it's there at all, you should be able to do
this with an Update query. BACK UP YOUR DATABASE FIRST!!! since this
may be tricky.

Create an Update query based on your table. Include the [PROPERTY
LOCATION] and [UNIT] fields; as a criterion on [PROPERTY LOCATION] use

LIKE "*C0*"

to restrict the records to those with a UNIT.

Then on the Update To row under [UNIT] put

MID([PROPERTY LOCATION], InStr([PROPERTY LOCATION], "C0"))

and under [PROPERTY LOCATION]

Trim(Left([PROPERTY LOCATION], InStr([PROPERTY LOCATION], "C0") - 1))


John W. Vinson[MVP]

 

[Mo Man]

Many thanks for your help!

 

[Mo Man]

John,

Many thanks for your help!

Mo