Wrong Data In Field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all,

I recieved a database that has the "Middle Initial" data combined with the
"First Name" data, which leaves the middle initial fields all blank.

Is there a way to move the "Middle Initial" data back into the correct
field. My database fields are:

First Name Middle Initial Last Name
------------ --------------- ------------

Thanks in advance, hope I explained this correctly.
 
Hello all,

I recieved a database that has the "Middle Initial" data combined with the
"First Name" data, which leaves the middle initial fields all blank.

Is there a way to move the "Middle Initial" data back into the correct
field. My database fields are:

First Name Middle Initial Last Name
------------ --------------- ------------

Thanks in advance, hope I explained this correctly.
Click to expand...

Assuming that:

1. Your First Names have values such as "John W." and "Ben" and "Kevin
A."
2. You don't mind two-word first names such as "Mary Jo" getting split
up (though the people with those names might; my friend Dolpha Mae
uses "Dolpha Mae" as her first name and dislikes being addressed as
Dolpha)

Create an Update query based on the table. Put a criterion on First
Name of

LIKE "* *"

to select only those records with two names in the First Name field.
Update First Name to

Left([First Name], InStr([First Name], " ") - 1)

and Middle Name to

Trim(Mid([First Name], InStr([First Name], " ")))

Run the query with the ! icon.

You *did* back up your database first, right? Oh, good.

John W. Vinson[MVP]
 
ASSUMING that your First Name field always has a space and a letter at the end
and no names like "Mary Anne L." then you could use something like;

UPDATE YourTable
SET [First Name] = Left([First Name],Instr(1,[First Name]," ")-1),
[Middle Initial] = Mid([First Name],Instr(1,[First Name]," ")+1)
WHERE [First Name] Like "* *"

Look up help on the Instr, Left, Right, and Mid functions.
 
John,
Here is another "Assuming" thought.
Assuming that all middle initials are space letter period formatted and that
anything that does not have that format is not a middle initial then you
could use
UPDATE YourTable
SET [First Name] = Left([First Name],Instr(1,[First Name],".")-1),
[Middle Initial] = Mid([First Name],Instr(1,[First Name],".")+1)
WHERE [First Name] Like "* .?"
HTH,
CF

John Spencer (MVP) said:
ASSUMING that your First Name field always has a space and a letter at the
end
and no names like "Mary Anne L." then you could use something like;

UPDATE YourTable
SET [First Name] = Left([First Name],Instr(1,[First Name]," ")-1),
[Middle Initial] = Mid([First Name],Instr(1,[First Name]," ")+1)
WHERE [First Name] Like "* *"

Look up help on the Instr, Left, Right, and Mid functions.

Sky said:
Hello all,

I recieved a database that has the "Middle Initial" data combined with
the
"First Name" data, which leaves the middle initial fields all blank.

Is there a way to move the "Middle Initial" data back into the correct
field. My database fields are:

First Name Middle Initial Last Name
------------ --------------- ------------

Thanks in advance, hope I explained this correctly.
Click to expand...
Click to expand...
 
Thanks to all of you as all your replies solved the problem. I have kept your
replies on my computer for future reference, hope you don't mind. Whoever you
guys are, I want you to know that your the reason I'm still at least half
sane. YOU ROCK!!!
 
Thanks to all of you as all your replies solved the problem. I have kept your
replies on my computer for future reference, hope you don't mind. Whoever you
guys are, I want you to know that your the reason I'm still at least half
sane. YOU ROCK!!!
Click to expand...

Thanks Sky! Glad we could help.

You might want to check out information about the MVP program at
http://mvp.support.microsoft.com/default.aspx.

John W. Vinson[MVP]
 
Back
Top