Last record replace comma with 'and'

  • Thread starter Thread starter pdemarais
  • Start date Start date
P

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
 
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
Click to expand...
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
 
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
Click to expand...
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
Click to expand...



--
 
Back
Top