Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    115

    Unanswered: Add & sign before last record

    I use this to concatenate multiple records into one filed.
    however, I would like to add & sign before last record.

    can anyone please modify this function?
    also, if there is a better way of this concatenate, please share it.

    Thank you

    Function ItemsString(RecordID As Variant, TableName As String, _
    FieldName As String) As String

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim Item As Long
    Dim ALLitem As String

    Item = ""
    ALLitem = ""

    RecordID = Nz(RecordID, 0)
    strSQL = "SELECT * FROM [" & TableName & _
    "] WHERE [" & FieldName & "] = " & RecordID & ";"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)

    If rs.RecordCount > 0 Then
    Do While Not rs.EOF
    Item = Nz(rs(1) + " ", "")
    ALLitem = ALLitem & Item

    rs.MoveNext
    Loop
    End If

    ItemsString = ALLitem
    End Function

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try this:
    Code:
    Function ItemsString(RecordID As Variant, TableName As String, FieldName As String) As String
    
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim strSQL As String
        Dim Item As String
        Dim ALLitem As String
        Dim lngCount As Long
        
        RecordID = Nz(RecordID, 0)
        strSQL = "SELECT * FROM [" & TableName & "] " & _
                 "WHERE [" & FieldName & "] = " & RecordID & ";"
        Set db = CurrentDb
        Set rs = db.OpenRecordset(strSQL)
        With rs
            If .RecordCount > 0 Then
                .MoveLast
                lngCount = .RecordCount - 1
                .MoveFirst
                Do While Not .EOF
                    Item = Nz(rs(1) + " ", "")
                    If .AbsolutePosition = lngCount Then
                        ALLitem = ALLitem & "&" & Item
                    Else
                        ALLitem = ALLitem & Item
                    End If
                    .MoveNext
                Loop
            End If
            .Close
        End With
        ItemsString = ALLitem
        Set rs = Nothing
        
    End Function
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you have a large number of rows then doing the movelast/movefirst shuffle to find the number of rows may attract a performance penalty.
    instead what you could do is defer the building of the concatenated string
    Code:
    Dim itemToAdd as string 'will hold the item to be added to the list retrospectively
    ItemToAdd = ""
    Allitem = ""
    With rs
        Do While Not .EOF
            allItem = allitem & ", " & itemtoadd 'add the previous item to the list
            itemtoadd = Nz(rs(1) + " ", "") 'set the next item to add
            .MoveNext
        Loop
    
        'ok so we have iterated through the loop
        'do a bit of tidying up
        if len(allitem)>0 then 'we have soemthing in our list
            allitem = allitem & " & " & itemtoadd
            allitem = mid(allitem,2) 'chop of the leading ", "
        else 'we only found one OR no rows
            allitem = itemtoadd
        endif
    End With
    .close
    is it significantly different to Sindho's suggestion.... no
    it does cater for the one or multiple items in the list problem
    it avoids movefirst/last and instead iterates through the recordset
    personally I think its a little easier code to look at, and as such may be a leetle easier to understand but thats just opinion
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •