?
Solved

copy part of record to another field

Posted on 2008-10-31
4
Medium Priority
?
543 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
[X]
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
  • 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 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

800 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