Two string manipulation questions

[LongWayFromHome]

Question 1:
I have a string of names separated with commas which go into a sentence:
strNameList = "Name1, Name2, Name3"
I want to insert " and" after the last comma, thus:
strNameList = "Name1, Name2, and Name3"

The only method I know is to test each character working backwards from the
right until I find the comma. This loop seems really inefficient:
Dim intCounter as Integer, strChar as String
intCounter = 0
strChar = "Z" 'initialize with arbitrary letter...
Do While strChar <> ","
strChar = Mid(strNameList , Len(strNameList ) - intCounter, 1)
intCounter = intCounter + 1
Loop

Is there a way to jump directly to the position of the right-most comma? If
not, is there a faster method than the step-wise counter I've devised?

Question 2:

Some of the names in strNameList have commas in them, such as "Name3, Jr."
To prevent the loop above from changing "Name3, Jr." to "Name3, and Jr.", I
have previously changed all name commas to "*", thus: "Name3* Jr." SO, after
I insert the "and" above, I am ready to change all instances of "*" back to
",". (Sometimes there are multiple "*" to replace.)

The only method I know to do this is the same kind of loop as I used above,
except this time I test every character in the entire string. Now that seems
REALLY inefficient.

Is there a straightforward function that does the job all at once?

Thanks!

 

[fredg]

Question 1:
I have a string of names separated with commas which go into a sentence:
strNameList = "Name1, Name2, Name3"
I want to insert " and" after the last comma, thus:
strNameList = "Name1, Name2, and Name3"

The only method I know is to test each character working backwards from the
right until I find the comma. This loop seems really inefficient:
Dim intCounter as Integer, strChar as String
intCounter = 0
strChar = "Z" 'initialize with arbitrary letter...
Do While strChar <> ","
strChar = Mid(strNameList , Len(strNameList ) - intCounter, 1)
intCounter = intCounter + 1
Loop

Is there a way to jump directly to the position of the right-most comma? If
not, is there a faster method than the step-wise counter I've devised?

Question 2:

Some of the names in strNameList have commas in them, such as "Name3, Jr."
To prevent the loop above from changing "Name3, Jr." to "Name3, and Jr.", I
have previously changed all name commas to "*", thus: "Name3* Jr." SO, after
I insert the "and" above, I am ready to change all instances of "*" back to
",". (Sometimes there are multiple "*" to replace.)

The only method I know to do this is the same kind of loop as I used above,
except this time I test every character in the entire string. Now that seems
REALLY inefficient.

Is there a straightforward function that does the job all at once?

Thanks!

If you have Access 2000 or newer, you can use the InStrRev() function
to add " and " after the last comma.

Paste the following into a Module.

Public Function AddText(StrIn As String) As String
Dim intX As Integer
intX = InStrRev(StrIn, ",")
StrIn = Left(StrIn, intX) & " and " & Mid(StrIn, intX + 1)
AddText = StrIn
End Function
==========

You can call it from a query:
NewColumn:AddText([FieldName])

You'll also want to check for [FieldName] being Null.

 

[LongWayFromHome]

I do have 2000, but I had never used InStrRev(). This takes me down to two
lines of code! Wow! I'm a believer now!

Thanks, Fred!