By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 2,093 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

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

P: 9
Hi Everyone, I am working with Access 2003.

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?

Thanks in Advance!
Dec 15 '06 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Everyone, I am working with Access 2003.

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?

Thanks in Advance!
Add two new fields to your table called City and PostCode

Then run the following update statement

Expand|Select|Wrap|Line Numbers
  1. UPDATE TableName SET City=Left([City Name], InStr([City Name], "(") - 1), PostCode=Mid([City Name], InStr([City Name], "(") + 1), Len([City Name]) - 1);
When you have checked that everything has updated alright you can delete the original field.

Mary
Dec 15 '06 #2

P: 9
Add two new fields to your table called City and PostCode

Then run the following update statement

Expand|Select|Wrap|Line Numbers
  1. UPDATE TableName SET City=Left([City Name], InStr([City Name], "(") - 1), PostCode=Mid([City Name], InStr([City Name], "(") + 1), Len([City Name]) - 1);
When you have checked that everything has updated alright you can delete the original field.

Mary
That was just an example of the format of the data in my field. The actual data I am working with encompasses different info but with the exact formats. Have 2 sets of information in one field. Another example would be, a field that has Car_Name and Car_ID, Car_ID is in brackets in the same field. (not sure if this is relevant)

I tried to implement your code, I replaced table name and i think city name - you mean the field name the data is under right? I got an error message:
[Syntax error in query experession.....]

Say the table name is: Main_Table
the field name: BusinessID
Dec 15 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
That was just an example of the format of the data in my field. The actual data I am working with encompasses different info but with the exact formats. Have 2 sets of information in one field. Another example would be, a field that has Car_Name and Car_ID, Car_ID is in brackets in the same field. (not sure if this is relevant)

I tried to implement your code, I replaced table name and i think city name - you mean the field name the data is under right? I got an error message:
[Syntax error in query experession.....]

Say the table name is: Main_Table
the field name: BusinessID
Try this ...

I had one too many closing brackets, sorry.

Expand|Select|Wrap|Line Numbers
  1. UPDATE [Main_Table] SET [City]=Left([BusinessID], InStr([BusinessID], "(") - 1), [PostCode]=Mid([BusinessID], InStr([BusinessID], "(") + 1, Len([BusinessID]) - 1);
Mary
Dec 15 '06 #4

P: 9
Try this ...

I had one too many closing brackets, sorry.

Expand|Select|Wrap|Line Numbers
  1. UPDATE [Main_Table] SET [City]=Left([BusinessID], InStr([BusinessID], "(") - 1), [PostCode]=Mid([BusinessID], InStr([BusinessID], "(") + 1, Len([BusinessID]) - 1);
Mary
How would code look like...If I want to break a field into 3.
sample data: let's say
red car (110294)

I need to split this field into 3 fields: color, vehicle, and vehicle ID, how much would the data chage?

The code worked - but I am getting a error message saying "There isn't enough disk space or memory to undo the data changes this action query is about to make." I have like 60gig of free space. I dont why this is happening.

My table base has like 900,000 records. I should be able to run the query!

Thanks
Dec 15 '06 #5

NeoPa
Expert Mod 15k+
P: 31,186
It will run - it's just saying you can't undo changes if you make them.
I just ignore that message when I see it.
Many MS messages are bogus - it probably just means that the query will run without Transactions.
Dec 16 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.