remove comma in access query
Posted on 2014-02-19
I have an access query that exports out as a pipe delimited file. I have no control how the databases are set up.
I have to have to have 2 fields, one last name, one first name. Sometimes when the name has a JR or a III (or any other suffix) after the name - there is a comma between the JR or the III. Sometimes the database entry is ok with no comma, some times not. I just depends on how it was entered in the database table.
Good: |SMITH|EDWARD H III|
BAD: |JONES|III, ROBERT D | BAD
In the database this is an example of how the name looks in the database
The Good entry: SMITH, EDWARD H III
The bad entry: JONES, III, ROBERT D
I have an iff then statement to split up the Last name and first name. What I need is to get rid of any commas that might be in PHM_CHARGES_ENHANCED.DR_NAME OR MAKE_PHM_CURES.DR_NAME
LAST NAME: IIf([PHM_CHARGES_ENHANCED.DR_NUMBER]="000000" Or IsNull([PHM_CHARGES_ENHANCED.DR_NUMBER]) Or [EC-CLN]<1,Left([MAKE_PHM_CURES.DR_NAME],[CU-CLN]),Left([PHM_CHARGES_ENHANCED.DR_NAME],[EC-CLN]))
FIRST NAME : IIf([PHM_CHARGES_ENHANCED]![DR_NUMBER]="000000" Or IsNull([PHM_CHARGES_ENHANCED]![DR_NUMBER]) Or [EC-CLN]<1,Mid([MAKE_PHM_CURES.DR_NAME],[CU-CFN]),Mid([PHM_CHARGES_ENHANCED.DR_NAME],[EC-CFN]))