Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Last record replace comma with 'and'

Discussion in 'Information Technology' started by pdemarais@gmail.com, Jul 28, 2006.

  1. pdemarais@gmail.com

    pdemarais@gmail.com
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Last Blog Blogs Oct 14, 2016
    That Pesky Last 1% Blogs Oct 12, 2016
    Self Songs Of The Last 10 Years Blogs Oct 2, 2016
    Difficult Areas Of Turban And How To Remedy It ? Is A Plastic Cup For Your Ears The Answer Health & Nutrition Jun 4, 2016
    Alone At Last Blogs May 1, 2016

  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Code interjected (no >)

    HTH

    Pieter

    <pdemarais@gmail.com> wrote in message
    news:1151435125.896537.99560@y41g2000cwy.googlegroups.com...
    >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
    >
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Code interjected (no >)

    HTH

    Pieter

    <pdemarais@gmail.com> wrote in message
    news:1151435125.896537.99560@y41g2000cwy.googlegroups.com...
    >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
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4079 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     

Share This Page