select portion of a record

[cardgunner]

I posted this in the table section however it is better suited here I think.

I have a field in a table that I imported in. This field has the first/last/ and Middle initial all in one. So name=Frank H. Urtz.

So I want to set my criteria to say something like give me everything up to the first " " as expr1. Give me everything from first " " to second " " as expr2. Give me everything thing from second " " to the end as expr3.

What is the the statement I want for this?

Cardgunner

 

[Uncle Gizmo]

Parsing Data

I would suggest you search the forum on the search term "Parse" or Parsing" I'm sure there is a lot of information on this subject, however if you have any problems please Repost.

 

[cardgunner]

Well now that I know what I'm trying to do, PARSE, there is alot of information out there.

Thank you.

However I have never wrote a function before and know nothing about code.

I'm trying to figure it out now but I'm still going need some help I fear.

 

[Uncle Gizmo]

>>>I'm trying to figure it out now but I'm still going need some help I fear.<<<

No problem, we all need help from time to time, particularly with MS Access, I don't think you ever stop learning about it!

 

[cardgunner]

Split Function

Ok, I'm totally lost and my head is swimming.

It seams there are at least 2 different ways to do this.

1 is thru an expression in a query using split([name]," ",0) which has not worked for me yet but I'm trying to figure it out.

2 is thru code. I haven't even started figuring this out because there has to be 100 posts through out the web and no 2 are alike, or similiar. I have no idea what Dim means or where to start or how to end or anything. This is actually what I have not learned yet and would love to learn to solve this thru code.

Correct me if I'm wrong but if I built a module with code it would be to use in a command button or something similar. I did go thru an excercise in VBA code
http://www.fontstuff.com/vba/vbatut04.htm

If I write a module for this is it going to do what I want?

What I want is create 2 new columns in table client called first and last. I want to have take the first name and Middle Intial if there is one in the name field and insert that into first. Then take what ever is left and insert that into last. I will only have to do this once, i think, because from now on it will be done right from the front end.

Like I said I would love to learn to do this in code and thru a function but where do I start and will I achieve the desired result?

http://www.tek-tips.com/faqs.cfm?fid=1823
http://www.access-programmers.co.uk/forums/showthread.php?p=440699
http://forums.whirlpool.net.au/forum-replies-archive.cfm/563160.html

 

[Uncle Gizmo]

Try this Example:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=ParseStuff.mdb

 

[Uncle Gizmo]

Parse function from Allen Browne

And a fully fledged function here that you may find useful