• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

copy part of record to another field

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
jaud
Asked:
jaud
  • 2
  • 2
1 Solution
 
dbbishopCommented:
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
 
jaudAuthor Commented:
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
 
dbbishopCommented:
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
 
jaudAuthor Commented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now