First Name Variations - Split MI from First Name

[IrishRed]

Hello,
I have tried all posts that I thought would resolve my issue and have not
been successful. I have data in a first name field that looks like this:

FName
JOYCE A
INGRID E
ROSALYN
CELESTE
KIMBERLY
ROBERT D.

Some have a middle iniitial in the first name field & some have middle
initial with a period. I have tried this in an update query
Left([Fname],InStr([Fname]," ")-1) which gives me only the first name but for
any of the entries where there is a space or period it removes the first name
completly.

Ideally I would end up with two fields: first name and middle initial in two
separate fields.

Thank you for your time.

 

[John Spencer]

Try concatenating a space onto the end of FName in the Instr call.

LEFT(FName,Instr(1,FName & " ")-1)

For middle initial
Mid(FName,Instr(1,FName & " ")+1)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

Hello,
I have tried all posts that I thought would resolve my issue and have not
been successful. I have data in a first name field that looks like this:

FName
JOYCE A
INGRID E
ROSALYN
CELESTE
KIMBERLY
ROBERT D.

Some have a middle iniitial in the first name field & some have middle
initial with a period. I have tried this in an update query
Left([Fname],InStr([Fname]," ")-1) which gives me only the first name but for
any of the entries where there is a space or period it removes the first name
completly.

Ideally I would end up with two fields: first name and middle initial in two
separate fields.

Thank you for your time.

Just use a criterion on FName of

LIKE "* *"

to have it apply only to those records which do have a blank.

 

[IrishRed]

Thanks for the response.

I am getting an invlid procedure call when I attempt to add teh space to the
end of the FName. I used the string that you supplied:
Left("FName",InStr(1,"FName" & " ")-1)

Try concatenating a space onto the end of FName in the Instr call.

LEFT(FName,Instr(1,FName & " ")-1)

For middle initial
Mid(FName,Instr(1,FName & " ")+1)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hello,
I have tried all posts that I thought would resolve my issue and have not
been successful. I have data in a first name field that looks like this:

FName
JOYCE A
INGRID E
ROSALYN
CELESTE
KIMBERLY
ROBERT D.

Some have a middle iniitial in the first name field & some have middle
initial with a period. I have tried this in an update query
Left([Fname],InStr([Fname]," ")-1) which gives me only the first name but for
any of the entries where there is a space or period it removes the first name
completly.

Ideally I would end up with two fields: first name and middle initial in two
separate fields.

Thank you for your time.

 

[John W. Vinson]

I am getting an invlid procedure call when I attempt to add teh space to the
end of the FName. I used the string that you supplied:
Left("FName",InStr(1,"FName" & " ")-1)

That's not what John supplied. Your quotemarks around FName are passing the
function the literal five-character text string FName - use [Fname] instead to
pass the value of the field.