Parsing Name Fields (1 Viewer)

[HJAMES]

I am using the following statement in an update query (which by the way I found in this forum) to pull out the first name from the FirstName field. The field could have a first name or a first name and a middle initial. If the field has a first name and niddle intital: John P then I get John. If the field only has a first name, I get #Error in the update field. I have thousands of records to move to a new Oracle table which has a first name field and a middle intial field.

FNAME: IIf([FirstName] Is Null," ",Left([FirstName],InStr(1,[FirstName]," ")-1))

Any help would be greatly appreciated.

Thank You

 

[Bat17]

Assuming that there is always a first name then
FNAME: IIf(InStr(1,[FirstName]," "),Left([FirstName],InStr(1,[FirstName]," ")-1),[FirstName])

If you have Null's we will need to expand it to allow for that.

Peter

 

[HJAMES]

Thank you so much - I've been working with that all morning - it works great!
I didn't think about it before but I will probably have the same problem with the middle initial since some of them don't have one. Can you please help me with that code.

 

[Bat17]

This assumes only 1 middle letter

MNAME: IIf(InStr(1,[FirstName]," "),right([FirstName],1),"")

John PD or John P D will get a bit more tricky

Peter

 

[HJAMES]

Thanks Peter - you're a life save