Extracting Names Problem (1 Viewer)

[Sohaila Taravati]

I am trying to extract the name of our [Shipper]. They way it has been put in looks like this: Doe, John. But sometimes there is no first name and only one name: Doe.
I was able to extract the first and last name with exactly the way I want it, but it shows me #Error when there only a last name. I am really getting frustrated. Any ideas anyone?

 

[The_Doc_Man]

If the data has been input through a form, trap cases like this in the form and don't let your user leave without storing a full name.

Basically, in the BeforeUpdate of the form, look at the field holding this information. Find the comma that separates last and first name.

Then extract them using the Left$() and Right$() functions.

Then check the length of each.

If either there was no comma or the first name is zero length or the last name is zero length, don't let the update complete.

You can block this off in the BeforeUpdate. Any other place might be trickier. (Not impossible, just trickier.)

It DOES require you to write some VBA code.

Now, as to what you do about the ones already present in error, ...

Beats me! Probably going to be a hand edit or nasty little update query.

 

[Sohaila Taravati]

Thank you Doc_Man for your quick response. Unfourtunatly, the shipper's name is being updated from our Unix system and the data entry is put there from our Unix also. So, I don't have any control over how the name has been put in, because this is a shipper name, and can be a business name, like IBM or AutoDesk and no first name and last names are involved. I have already done the extract using the Left$() and Right$() functions and was trying to avoid your last suggestion "THE NASTY UPDATE".
Oh well, Unless somebody else can come up with a better idea I will live with the what I have

 

[Rich]

How do you extract /combine the fields?

 

[The_Doc_Man]

Welcome to the world of retrofits. Also pitching fits, because that is what it leads to. Notice my little title under my web name? I'm all to familiar with cleaning up other people's messes.

This kind of problem is all too common.

If you were comfortable in VBA, I would suggest writing a public VBA function that could be included in a query. Perform the required extractions, reformat whatever you have, and return the resultant reformatted string, taking into account the differences when you have a company name rather than a person.

Or,... just don't format it. When someone complains, refer them to the Unix programmers who give you the sow's ear, then walk away muttering about silk purses....

Put the blame where it belongs, in other words, and let the pressure for the reformatting occur in THEIR laps. Politically, not nice. But it is the ONLY way you are going to get better data - or relief from the requirement.

 

[Sohaila Taravati]

I call this the last Name of the shipper:
LShipper: Left([Shipper],InStr(1,[Shipper],", ")-1)

And First Name of the Shipper:
FShipper: Right(Trim([Shipper]),Len(Trim([Shipper]))-InStr(1,[Shipper]," "))

Then have another files called NewShipper that gives me First name and last name:


NewShipper: ProperCase([FShipper]) & " " & ProperCase([LShipper])
It changes the Doe, John to John Doe for me. But if there is only Doe then I get #Error

 

[Rich]

Well if Docs advice is not a viable proposition I would use the IsNull, Not IsNull functions