Question Trim Problem, Middle Names

[Alan Forman]

Hi Guys

I've got a field that contains a clients First Name and any middle names.

e.g. Bill Richard Stephen

I'd like to copy the first name (up to the first space) to a field called 'First_Name' (Bill), and any other names to another field called 'Middle_Names' (Richard Stephen).

A client may have one, two, three or no other names.

I've tried .....

Trim_Space: Left([First_Name],(InStr(1,[First_Name]," "))-1)

which sort of does the first name but returns #error if there is no middle names.

Any help appreciated.

Thanks

Alan

 

[Mr. B]

:

Trim_Space: IIf(InStr(1,[First_Name]," ")>0,Left([First_Name],InStr(1,[fname]," ")-1),[First_Name])

 

[Alan Forman]

That's fantastic, works perfectly.

What I need now is to delete the result of that field from the [First_Name] field, which will leave me with just the middle names.

Or

Delete everything up to, and, including the first space in the [First_Name] field.

Any thoughts on that bit.

Thanks very much, a day and a half of head scratching for me, 10 mins for you.

 

[Mr. B]

You can create a MiddleName field and populate that field using the following statement:

MidName: IIf(InStr(1,[FName]," ")>0,Mid([fname],InStr(1,[FName]," "),Len([fname])-InStr(1,[FName]," ")+1),"")

This will do the same thing that you are doing with the original statement and populate this MiddleName field.

 

[Alan Forman]

That's brilliant, Thank You

You've saved me loads of time.

Thanks Again

 

[Mr. B]

Glad to help.

Click the "Thanks" button if you don't mind.