In joining 2+ records together seperated by commas replace last comma with 'and'

[pdemarais]

I have the following code which puts 2 records together by "JobNum" and

seperates with a comma in the field. I would like to for the last
string added to put the word 'and' instead of a comma. Can anybody
help?

Public Function CostInstall() As Boolean
On Error Resume Next


Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String
Dim strJobNum As String, strRoom As String


Set db = CurrentDb()


sSQL = "SELECT JobNum, CostDesc FROM Add_Cost " _
& "ORDER BY JobNum ASC"
Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)


If Not rst.BOF And Not rst.EOF Then
rst.MoveFirst
strJobNum = rst!JobNum
strRoom = rst!CostDesc


rst.MoveNext
Do Until rst.EOF
If strJobNum = rst!JobNum Then
strRoom = strRoom & ", " & rst!CostDesc
Else
sSQL = "INSERT INTO AddCostCopy (JobNum, CostDesc) " _
& "VALUES('" & strJobNum & "','" & strRoom & "')"
db.Execute sSQL
strJobNum = rst!JobNum
strRoom = rst!CostDesc
End If
rst.MoveNext
Loop


' Insert Last Record
sSQL = "INSERT INTO AddCostCopy (JobNum, CostDesc) " _
& "VALUES('" & strJobNum & "','" & strRoom & "')"
db.Execute sSQL
End If


Set rst = Nothing
Set db = Nothing


End Function

 

[John Nurick]

If you have a string like this
S = "one, two, three, four"

you can use an expression like this to replace the last
comma with " and":

Left(S, InStrRev(S, ",") - 1) & " and" & Mid(S, InStrRev(S, ",") + 1)