Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

copy part of record to another field

Posted on 2008-10-31
4
540 Views
Last Modified: 2012-05-05
Hello,
In the database I am working on, the Last Name & First Name fields have been used to store all naming information about the contacts.  In the last name field there are prefixes, organizations names, etc.
In the first name field there are more prefixs, suffixs, and spouse names.
I specificly need help coping only the spouse name to a new field, but I hope the solution can work for all the date cleaning problems I have with these two fields.
The data in the FIRST NAME field I need to parse out is mainly in the format of "name & name"
I can't find out how to copy just the text after "& name"
I have tried Instr, but am not knowledgeable enought about the details of Access to use it (does it go in the design part of a query, is it used in making a SQL statement, or does it need to be in a modules?)
(I know the names of the different parts of Access, but not how to use them, yet)
Thank you.
0
Comment
Question by:jaud
  • 2
  • 2
4 Comments
 
LVL 15

Expert Comment

by:dbbishop
ID: 22856092
The following should do it. The IIf() function first uses the InStr function to determine if there is an '&' in the column. If so, the Mid() function is used to pull the data following the '&' (+2 to skip the character and the space following it. If there is no '&', the else portion of the IIF() function returns the whole name.

SELECT IIf(InStr([FirstName],"&") > 0, Mid([FirstName], InStr([Firstname], "&") + 2), FirstName) AS Name
FROM tblEmployee;
0
 

Author Comment

by:jaud
ID: 22857635
Thanks dbbishop,

I copied your code, modified it for my values, and ran it.  I appreciate you telling me what different parts and functions of your code are for and what they do.  

I made a new query in design mode, went to view, selected SQL and entered the following:

SELECT IIf(InStr([FIRSTNAME],"&") > 0, Mid([FIRSTNAME], InStr([FIRSTNAME],
"&") + 2), FIRSTNAME) AS SPOUSENAME
FROM tblPeople;

The query returned the results of all names in the FIRST NAME field, not just the results of the characters after the "&".   I need the query to only return results for the records after the "& " sign.  I guess I need the the 'else' portion of the IIF() function to leave the data as it is and not return a value if there is no "&".  (however, I did not see an "else" portion in the code you gave me).  

I also did not see how the query would update the results to the SPOUSENAME field.  The query just showed me results.  I can add the id field to this query, make a table tblSpouse, then join tblSpouse and tblPeople in a query via the id field and run an update, but that would leave me with the spouse name in the FIRSTNAME field and the SPOUSENAME field.  Then I could go to the table and do a find a replace to get rid of the "& *".

The make table solution would work, but I am really hoping a query can select the text I want, update it to the field I want, and then delete it from the original record.  I will need to run this query and all associated procedures (find & replace) many times to move other parts of data to the correct field (middle names/ initials in particular).
0
 
LVL 15

Accepted Solution

by:
dbbishop earned 500 total points
ID: 22858262
I just provided the code to parse the FirstName for the SPouseName. To actually update and the delete, I would recommend two passes, although it can be done with one update. The first pass will update SpouseName. You can then check and make sure it ran properly, then run the second update to remove the SpouseName from FirstName.

As always, I recommend that you work with a backup copy of your table.

UPDATE tblPeople
SET SpouseName = Mid([FIRSTNAME], InStr([FIRSTNAME], "&") + 2)
WHERE InStr([FIRSTNAME],"&") > 0

UPDATE tblPeople
SET FirstName = LEFT([FIRSTNAME], InStr([FIRSTNAME], "&") - 1)
WHERE InStr([FIRSTNAME],"&") > 0

-- Both updates can be done at once in this manner:
UPDATE tblPeople
SET SpouseName = Mid([FIRSTNAME], InStr([FIRSTNAME], "&") + 2),
       FirstName = LEFT([FIRSTNAME], InStr([FIRSTNAME], "&") - 1)
WHERE InStr([FIRSTNAME],"&") > 0
0
 

Author Closing Comment

by:jaud
ID: 31512275
Great solution.  The explaination of the terms will help me learn how to do these things myself.
I will have more questions soon.   Answers such as these are great.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question