How do I extract a portion of text from a field in MS Access 2003

I have a field in an access 2003 table called [FullName] that contains data such as:
Ballard, Daniel
Beckstrom, Ola Elaine
Carroll-Parker, Karen
Mathews, Michael D
etc.,

I need to run a query that creates a new field called [FirstName] that would pull out only the first name from the data. Using the examples above, I would want: Daniel, Ola, Karen & Michael placed in the new field called [FirstName].

I tried using this: Trim(Mid([FullName], InStr(1, [FullName], " ") + 1, IIf(InStr(InStr(1,[FullName], " ") + 1, [FullName], " ") = 0, 0, InStr(InStr(1, [FullName], " ") + 1, [FullName], " ") - InStr(1, [FullName], " "))))

but that didn't work. I need help. Thank you!
seala56Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Split is a better choice:

FirstName = Split(Split(FullName, ",")(1), " ")(1)

/gustav
0
Rey Obrero (Capricorn1)Commented:
you need a UDF to do this.
place this codes in a regular module

function getFirstName(vName as string)

getFirstName=split(vname," ")(1)

end function


to use in a query

select FullName, getFirstName([FullName]) as FirstName from tableX

this will only work if all your info are written the way you have posted it above.
0
Gustav BrockCIOCommented:
You could use this function which will return a clean Null in case of invalid full names:
Public FirstName(Byval FullName As String) As Variant
    
    FirstName = Null

    On Error Resume Next
    FirstName = Split(Trim(Split(FullName, ",")(1)), " ")(0)

End Function

Open in new window

/gustav
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

seala56Author Commented:
I am just trying to run a simple query from a table. How would I do that?

For example: FName:Split(Split(FullName, ",")(1), " ")(1) ???
0
Rey Obrero (Capricorn1)Commented:
seala56,

use the function and query i posted above
0
Gustav BrockCIOCommented:
> How would I do that?

Problem is that Split(something, ",")(0) is not understood in a Query.

VBA does, however, so you have to create a small function to wrap the expression. Then use this function in your query.

/gustav
0
seala56Author Commented:
I'm either not understanding you or you're not understanding me LOL. I have a table in MS Access 2003 that has many fields. The FullName field contains names such as:
Ballard, Daniel
Beckstrom, Ola Elaine
Carroll-Parker, Karen
Mathews, Michael D
etc.,

I clicked on "Create" >> "Query Wizard" >> "Simple Query Wizard. I selected my table from the dropdown and then moved all fields over the right so they would all be in the results. I then clicked "Next" >> "Next" , selected "Modify the Query Design" and I'm in design view. If I go to SQL View, I see this: SELECT [LD2014-4Ds].[SpcList], [LD2014-4Ds].[Organization], [LD2014-4Ds].[FullName], [LD2014-4Ds].[LastName], [LD2014-4Ds].[FirstName], [LD2014-4Ds].[Address], [LD2014-4Ds].[City], [LD2014-4Ds].[State], [LD2014-4Ds].[Zip]
FROM [LD2014-4Ds];

So in either SQL view or in design view I need to create a field called [FirstName] which I need to populate by extracting it from the [FullName] field which already exists in the table.

So, how do I go about doing what you're telling me to do? I don't know where to put that information other than to add a column in design view type in FirstName: and go to the builder to insert the query. I am not an expert, only a novice. Thank you.
0
Rey Obrero (Capricorn1)Commented:
@seala56,

do you know how to get to the VBA window?

if not, see this link first,   http://www.functionx.com/vbaccess/Lesson05.htm
0
seala56Author Commented:
According to those instructions, that's " To access the module of a form or report". I'm creating a query.
0
Rey Obrero (Capricorn1)Commented:
how about going thru the content of the page...
0
seala56Author Commented:
Content of the page? I've tried everything and when in a query I see nothing to get me into a VBA window.
0
Rey Obrero (Capricorn1)Commented:
hit the keys

Alt and F11

do you see the VBA window?
0
seala56Author Commented:
yes, now what?
0
Rey Obrero (Capricorn1)Commented:
read my post at http:#a40830215
0
seala56Author Commented:
I still don't understand where I'm supposed to put it and how to use it for my query
0
Gustav BrockCIOCommented:
Open Access and your database.
Press Ctrl+G
Select menu Insert, Module.
Copy the function from here (Select All): http:#a40830240
Paste it into the module.
Select menu Debug, Compile
Select menu File, Save

Open your query in SQL design view.
Copy this:

    SELECT * FirstName([FullName]) As NewFullName From [LD2014-4Ds]

and paste it in to overwrite the SQL.
SaveAs the query and run it.

/gustav
0
Rey Obrero (Capricorn1)Commented:
see this sample db
the function getFirstName is in module1

open Query1 to see how the function was used.
sealaDB.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
seala56Author Commented:
Yay! That worked in my db - Thanks!
0
seala56Author Commented:
Using the same information, how would extract the LastName from the full name? (Basically everything prior to the , ) I tried this: LastName:Left([FullName],InStr(1,[FullName],",")-1) but I keep getting an error that says Compile error in query expression.
0
Rey Obrero (Capricorn1)Commented:
@seala56

don't forget to close the thread..

see this link for instructions
http://support.experts-exchange.com/customer/portal/articles/608621-how-do-i-accept-a-comment-as-my-solution-
0
seala56Author Commented:
Using the same information, how would extract the LastName from the full name? (Basically everything prior to the , ) I tried this: LastName:Left([FullName],InStr(1,[FullName],",")-1) but I keep getting an error that says Compile error in query expression.
0
Rey Obrero (Capricorn1)Commented:
here see Query1 and getLastName function in module 1
sealaDB.mdb
0
seala56Author Commented:
Ok - Great! Thanks so much!
0
seala56Author Commented:
Took awhile to get on the same page with the expert but I did end up with a solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.