string function for seaching field with lastname,firstname middle init

[mbfloyd]

Hello,
I am trying to use a field that contains {lastname, firstname middle} to create a report. I want to create a last name search parameter. But how do I do it if what I'm searching on is the left part of a field. I hope I explained this clearly. I am using crystal 8.5.

thanks in advance.

 

[Thadeus]

Assuming the last name is always followed by a comma as you displayed, try:

Left({FULLNAMEFIELD},(InStr({FULLNAMEFIELD},",&quot-1))


Thadeus

 

[synapsevampire]

And if it does not have a comma, then you can use the same approach substituting a space, though you'll fail on a few:

Left({FULLNAMEFIELD},(InStr({FULLNAMEFIELD}," &quot-1))

Won't work for: De La Hoya Oscar

I used to write fairly elaborate code to clean up lists, and there were a myriad of rules that I applied to aid in this effort.

I'd do a list clean up if possible, using numerous rules, and then flag each suspect field (a quick and dirty sanity check would be 2 letter names, names with more than 3 spaces, etc.).

-k

 

[mbfloyd]

Thanks for your quick responses, I am using the formula suggested in the first response because the field does have a comma after the last name. It works perfect. THANKS ALOT However the second response suggests a list clean up. How would I proceed with a list cleanup?

Thanks

 

[synapsevampire]

A cleanup would depend on the database being used, and the tools at your disposal.

Storing last and first and middle names in the same column is generally a bad idea, so I would add in at least 3 columns to the table:

Last
First
Middle
(you might also add in Prefix and suffix too, to handle things like Mr., Mrs., Dr, III, PhD, etc...)

Add one other boolean field called bad temporarily so that during the parsing process you can quickly identify potentially incorrect rows.

Then write code to parse it out, perhaps even leveraging a names database, then hire a bright young admin to go through the potentially bad ones and manually clean them up.

Then drag you database architect over to the nearest Dennys and see that he/she applies for an appropriate job to their skill level

-k

 

[mbfloyd]

OK..I understand. Unfortunately I am using SAP r/3 Tables for reporting and I know its an Oracle database engine, but the SAP layer allows no access to the database except through basis and that would be a nightmare getting authorization. But I will keep the suggestion in mind just in case there is a chance they will allow a change.

Thanks alot

M