Thread: How could you divide the information in a single field into 2?

1. Registered User
Join Date
Nov 2006
Posts
67

Unanswered: How could you divide the information in a single field into 2?

Let's say I have a field on my Main table, it contains the city name and the postal code in brackets. How do I make it so I could either delete all the postal codes in my field or divide this field into 2, one has all the cities and the other has all the postal codes?

2. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941

3. Registered User
Join Date
Nov 2006
Posts
67
Originally Posted by RedNeckGeek
Data looks like:
Toronto (L2K 1Y5)
Ottawa (K4J 3H3)
...

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
run an update query that truncates the cityname at the left bracket

have a look at the string functions
especially the instr, left\$,right\$,mid\$

5. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764
Here's some of the grunt work for you:

Dim CityPostalCode as String

CityPostalCode = "Ottawa (K4J 3H3)"

left(CityPostalCode,( instr(CityPostalCode," ")-1)) returns Ottawa

right(CityPostalCode,(len(CityPostalCode)-( instr(CityPostalCode," ")))) returns

(K4J 3H3)

6. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
left(CityPostalCode,( instr(CityPostalCode," ")-1)) returns Ottawa
true, but it also returns 'New' from 'New York (123456)'

?? safer:
left(CityPostalCode,( instr(CityPostalCode,"(")-2))

??? safer and faster
left\$(CityPostalCode,( instr(CityPostalCode,"(")-2))

???? safest and faster:
trim(left\$(CityPostalCode,( instr(CityPostalCode,"(")-1)))

and, even tho it is very ugly:
mid\$(CityPostalCode, instr(CityPostalCode, "(") + 1, instr(CityPostalCode, ")") - instr(CityPostalCode, "(")-1)
returns '123456' from 'New York (123456)'

izy

7. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764
True, I only checked against his examples. Of course, New York doesn't have a Canadian postal code! LOL! Good catch, izy!

8. Registered User
Join Date
Nov 2006
Posts
67
Originally Posted by izyrider
left(CityPostalCode,( instr(CityPostalCode," ")-1)) returns Ottawa
true, but it also returns 'New' from 'New York (123456)'

?? safer:
left(CityPostalCode,( instr(CityPostalCode,"(")-2))

??? safer and faster
left\$(CityPostalCode,( instr(CityPostalCode,"(")-2))

???? safest and faster:
trim(left\$(CityPostalCode,( instr(CityPostalCode,"(")-1)))

and, even tho it is very ugly:
mid\$(CityPostalCode, instr(CityPostalCode, "(") + 1, instr(CityPostalCode, ")") - instr(CityPostalCode, "(")-1)
returns '123456' from 'New York (123456)'

izy

Since I am soo new in access. These are the codes for an update statement right? But I am getting syntax errors.

9. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
Originally Posted by tialongz
Since I am soo new in access. These are the codes for an update statement right? But I am getting syntax errors.
Noooo ... Iz was demonstrating generically. You'll have to do something like:

UPDATE SomeTable SET Whatever={Split Mechanism of Whatever Here}, WhateverElse={The leftovers cleaned up};

And yes, I know, TOTALLY unhelpful ...

10. Registered User
Join Date
Nov 2006
Posts
67
Originally Posted by M Owen
Noooo ... Iz was demonstrating generically. You'll have to do something like:

UPDATE SomeTable SET Whatever={Split Mechanism of Whatever Here}, WhateverElse={The leftovers cleaned up};

And yes, I know, TOTALLY unhelpful ...
ahah..someone is cheerful this afternoon.

Yes I did that.
something like:
The field with all the data: say EBusinesses

Just for the record, treat me like someone who is trying to become an expert in access over night.
Last edited by tialongz; 12-15-06 at 16:20.

11. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
you have changed the rules a little.
[businessID]= i also have No idea what goes here since you don't explain.

going back to trying to parse something like:
'New York (123456)'
into:
'New York' and '123456'

Code:
```UPDATE TheTableName SET
City = trim(left\$(CityPostalCode,(instr(CityPostalCode,"(")-1))),
ZIP = mid\$(CityPostalCode, instr(CityPostalCode, "(") + 1, instr(CityPostalCode, ")") - instr(CityPostalCode, "(")-1)```
izy

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•