Get Last Name from (Lastname Firstname) 1

[Hillbillie]

This has been posted, but is a little different than what I can understand.

Here is what I have.

A form that has a field called name. This field has lastname space first name ie "Johnson Larry"

I have a 2 new fields I made that are empty. Called firstname and lastname.

I want to populate these fields from the name field.

I only have 3300 records, so when I click on next record, these fields will automatically get the information.

Thanks,

 

[evalesthy]

There are lots of posting in these fora about parsing text strings. If you do a search on Parsing, Strings, Name, Last Name, etc. you should find what you are looking for.

 

[PHV]

Have a look at InStr, Left and Mid

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are ya Hillbillie . . .

[blue]   Dim idx As Integer
   
   idx = InStr(Name, " ")
   FirstName = Right(Name, Len(Name) - idx)
   LastName = Left(Name, idx - 1)[/blue]

See Ya! . . . . . .

 

[evalesthy]

Not exactly your situation - but lots of good ideas you can adapt:

thread701-794191

 

[Hillbillie]

Thanks everyone for the quick responses. TheAceMan1 your solution worked great.

Appreciate it...

 

[fneily]

As an aside: does your table field contain both lastname and firstname? eg. Johnson Larry in one field? If so, may I recommend a process called Normalization which is extremely important to creating Access or any relational databases. If you went through this process, the very first step or "first normal form" would have solved this situation. In my experience with major companies and government agencies, their "Access" people create databases without going through the Normalization process and problems, like yours, eventually arise.

 

[Hillbillie]

This is so true fneily. My problem was this data was imported from an excel file that did not have the names seperated.

When I design databases, I do my best to think ahead and that helps, but there are always things I dont think of.

I am currently taking over a database that was created on Foxpro and has been "partially" converted over to access. It has many bugs and problems that I work out as I find them.

Thanks again.

 

[fneily]

Actually, that's an example I use for my classes because I've seen it hundreds of times in the real world - people import or copy and paste an excel spreadsheet into an Access table. WRONG. They're two different internal structures. After the excel data is imported, it should then be normalized. The outcome it usually more than one Access table.
The trouble is that you have to deal with it. You have my sympathy.

 

[SilentAiche]

fneily,

Have a star.

As an Access amateur (a relative term, of course, and I am, or so I'm told, rightly self-deprecating), your advice was a good reminder. Normalization is key.

Well and generously said.

Tim

[blue]______________________________________________________________
I love logging onto Tek-Tips. It's always so exciting to see what the hell I
said yesterday. [/blue]