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

How to separate the contents of a column in a table

P: 10
Hello,

Using Access 2003, I have a database that holds names personal details of people. I have a requirement to hold first name and middle names in separate columns.

I would like to search the first name field and as soon as space is found remove the rest of the data to a new column

First Name
Sally Anne

becomes

First Name
Sally

Middle Name
Anne


What is the easiet of doing this?
I thought about creating a query that looks at the first name column but I don't know the function I can use.

Please help.

Thanks
May 9 '07 #1
Share this Question
Share on Google+
9 Replies


JConsulting
Expert 100+
P: 603
Hello,

Using Access 2003, I have a database that holds names personal details of people. I have a requirement to hold first name and middle names in separate columns.

I would like to search the first name field and as soon as space is found remove the rest of the data to a new column

First Name
Sally Anne

becomes

First Name
Sally

Middle Name
Anne


What is the easiet of doing this?
I thought about creating a query that looks at the first name column but I don't know the function I can use.

Please help.

Thanks
Add a Middle name field to your table, then run an update query like so
J

Expand|Select|Wrap|Line Numbers
  1. UPDATE testtbl SET testtbl.midname = IIf(InStr([name]," "),Mid([name],InStr([name]," ")+1,Len([name])-InStr([name]," ")),Null);
  2.  
May 9 '07 #2

JConsulting
Expert 100+
P: 603
were you also wanting to remove the initial from the first name field?
May 9 '07 #3

JConsulting
Expert 100+
P: 603
were you also wanting to remove the initial from the first name field?
this will update the name field too. Make a table backup first to test
J

Expand|Select|Wrap|Line Numbers
  1. UPDATE testtbl SET testtbl.midname = IIf(InStr([name]," "),Mid([name],InStr([name]," ")+1,Len([name])-InStr([name]," ")),Null), testtbl.Name = IIf(InStr([name]," "),Left([name],InStr([name]," ")),[name]);
  2.  
May 9 '07 #4

P: 10
Thanks for the advice but I'm having trouble inputting the command.
My query is using the Access design view and I set Expression1 to Middle Name and the in the update to field I add from IIF but this returns an error about the statement is missing something

I have also treid using the sql view to input the command but it says there is a problem with the syntax.

I've been using

Expand|Select|Wrap|Line Numbers
  1. UPDATE My test SET My test.middlename = IIf(InStr([first names]," "),Mid([first names],InStr([first names]," ")+1,Len([first names])-InStr([first names]," ")),Null);
My test - name of the table
middlename - should be the new column.

Where am I going wrong?
May 9 '07 #5

JConsulting
Expert 100+
P: 603
Thanks for the advice but I'm having trouble inputting the command.
My query is using the Access design view and I set Expression1 to Middle Name and the in the update to field I add from IIF but this returns an error about the statement is missing something

I have also treid using the sql view to input the command but it says there is a problem with the syntax.

I've been using

Expand|Select|Wrap|Line Numbers
  1. UPDATE My test SET My test.middlename = IIf(InStr([first names]," "),Mid([first names],InStr([first names]," ")+1,Len([first names])-InStr([first names]," ")),Null);
My test - name of the table
middlename - should be the new column.

Where am I going wrong?
Standard naming conventions dictate that you DO NOT create field names or table names with spaces or any non-alphaNumeric characters. If you have one, you must surround it in brackets.

Expand|Select|Wrap|Line Numbers
  1. UPDATE [My test] SET [My test].middlename = IIf(InStr([first names]," "),Mid([first names],InStr([first names]," ")+1,Len([first names])-InStr([first names]," ")),Null), [My test].[first names] = IIf(InStr([first names]," "),Left([first names],InStr([first names]," ")),[first names]);
  2.  
May 9 '07 #6

P: 10
Thanks, I've amended the code and it now looks like this

Expand|Select|Wrap|Line Numbers
  1. UPDATE [testtbl] SET [testtbl].midname = IIf(InStr([firstnames]," "),Mid([firstnames],InStr([firstnames]," ")+1,Len([firstnames])-InStr([firstnames]," ")),Null), [testtbl].[firstnames] = IIf(InStr([firstnames]," "),Left([firstnames],InStr([firstnames]," ")),[firstnames]);
However, this result returned two columns, midname (empty) and firstnames(containing both first and middlenames)
May 9 '07 #7

JConsulting
Expert 100+
P: 603
Thanks, I've amended the code and it now looks like this

Expand|Select|Wrap|Line Numbers
  1. UPDATE [testtbl] SET [testtbl].midname = IIf(InStr([firstnames]," "),Mid([firstnames],InStr([firstnames]," ")+1,Len([firstnames])-InStr([firstnames]," ")),Null), [testtbl].[firstnames] = IIf(InStr([firstnames]," "),Left([firstnames],InStr([firstnames]," ")),[firstnames]);
However, this result returned two columns, midname (empty) and firstnames(containing both first and middlenames)
Aren't your field names [First Name] and [MiddleName]? and isn't your table name [My test]?

You should be able to just plug those names in and you'll be good to go.
May 9 '07 #8

P: 10
Thanks for your help, that's worked just fine.

This is a great site
May 10 '07 #9

JConsulting
Expert 100+
P: 603
Thanks for your help, that's worked just fine.

This is a great site
Anytime.
J
May 10 '07 #10

Post your reply

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