Thread: Add & sign before last record
11-26-13, 14:52 #1Registered User
- Join Date
- Jan 2004
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.
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
ItemsString = ALLitem
11-26-13, 16:43 #2Moderator
Provided Answers: 14
- Join Date
- Mar 2009
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 FunctionHave a nice day!
11-27-13, 04:34 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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
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
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 opinionI'd rather be riding on the Tiger 800 or the Norton