Solved

copy part of record to another field

Posted on 2008-10-31
4
537 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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 …

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now