Last record replace 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

 

[Pieter Wijnen]

Code interjected (no >)

HTH

Pieter

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 Dim Pos As Long

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

For pos = Len(strRoom) To 1 Step -1
If Mid(strRoom,pos,1) = "," Then
strRoom = Left(strRoom,pos-1) & "And" & Mid(strRoom,pos+1)
Exit For
End If
next ' pos

 

[Pieter Wijnen]

Code interjected (no >)

HTH

Pieter

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 Dim Pos As Long

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

For pos = Len(strRoom) To 1 Step -1
If Mid(strRoom,pos,1) = "," Then
strRoom = Left(strRoom,pos-1) & "And" & Mid(strRoom,pos+1)
Exit For
End If
next ' pos

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

--