Trim

[AShi]

I would like to format name Bowser, Corinna, M.D. to Bowser Corina, M.D.

 

[Wayne-I-M]

Create a new query as paste this into the sql

SELECT Replace([TableName]![FieldName],",","") AS New
FROM TableName;

Change TableName and FieldName to what they really are

 

[Steve Schapel]

Wayne,

I don't think that will work, as AShi wants to retain the second comma.
And also wants to remove an "n" from the name apparently.

AShi:
Can you tell us whether all records you will be applying this to will
have exactly the same structure, i.e. 3 segments separated by commas,
and you simply want to remove the comma between the first and second
segments?

 

[Wayne-I-M]

Oh yeah - didn't see that anout the 2nd comma - or the removal of the 2nd "n"

Thanks Steve.

Sorry but can't concetrate at the moment - can you belive that england are
two nil down after only 10 mins. Oh my god.

Anyway, AShi ignor my crap answer

--
Wayne
Manchester, England.

Wayne,

I don't think that will work, as AShi wants to retain the second comma.
And also wants to remove an "n" from the name apparently.

AShi:
Can you tell us whether all records you will be applying this to will
have exactly the same structure, i.e. 3 segments separated by commas,
and you simply want to remove the comma between the first and second
segments?

--
Steve Schapel, Microsoft Access MVP

Create a new query as paste this into the sql

SELECT Replace([TableName]![FieldName],",","") AS New
FROM TableName;

Change TableName and FieldName to what they really are

 

[Jeff Boyce]

I can't tell what data (fields) you have to start with... it all starts with
the data!

Do you have:
[Honorific]
[FirstName]
[LastName]

or do you have:
[ANameField]
that contains the entire string "Bowser, Corrina, M.D."?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[AShi]

I have [ANameField]
that contains the entire string "Bowser, Corrina, M.D." I need to trim it
to Bowser Corrina
i forgot to mentionlast time that I need to trim suffix.

I can't tell what data (fields) you have to start with... it all starts with
the data!

Do you have:
[Honorific]
[FirstName]
[LastName]

or do you have:
[ANameField]
that contains the entire string "Bowser, Corrina, M.D."?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would like to format name Bowser, Corinna, M.D. to Bowser Corina, M.D.

 

[Jeff Boyce]

If you are absolutely, positively certain that EVERY string in [ANameField]
is formatted the same way (i.e., LastName Comma Space FirstName Comma Space
Suffix) then you can use the Left(), Mid(), Right() and InStr() functions to
parse the pieces out, then use concatenation to put them back together.

Or you could use the Replace() function (I believe already mentioned) to
remove the commas. If the "suffix" always follows a space following the
FirstName, you can use the above mentioned functions to take only the
portion up to that last space.

However, if there are ANY differences, you'll need to employ USB (using
someone's brain).

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have [ANameField]
that contains the entire string "Bowser, Corrina, M.D." I need to trim it
to Bowser Corrina
i forgot to mentionlast time that I need to trim suffix.

I can't tell what data (fields) you have to start with... it all starts
with
the data!

Do you have:
[Honorific]
[FirstName]
[LastName]

or do you have:
[ANameField]
that contains the entire string "Bowser, Corrina, M.D."?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would like to format name Bowser, Corinna, M.D. to Bowser Corina,
M.D.