Howdy folks! Good evening/Goodmorning...
So i am trying to concatenate a field that appears in X number of rows...
I came across this sample dB attached Database1.mdb
Basically when you run the query it concatenates all the "Markets" Entries into one field...
When I have tried to adopt this method in my dB I am getting allsorts of weird and wonderful runtime errors galore and I can't see an obvious mistake on my part and I was hoping for an eagle eye to call me a muppet and tell me to change this to that.....
[tblCustomFieldsSKU] = Table where all the fields are stored that require concatenating... The field is called [CustomField] This is on a subform called [tblEMGSKUMasterTable]
[EMGSKUID] links both tables and is the driving force that determines which rows are concatenated. So all [EMGSKUID]=4722 records, concatenate the field called [CustomField]
The Module code is as follows.... (and this is way over my head)
Code:
Option Compare DatabaseOption Explicit
Function Concatenate(Gettbl As String, GetKey As String, KeyValue As String, FieldConct As String) As String
Dim Rst As DAO.Recordset, MySql As String
Concatenate = ""
MySql = "SELECT * FROM " & Gettbl & " WHERE (((" & Gettbl & "." & KeyValue & ")='" & GetKey & "'));"
Set Rst = CurrentDb.OpenRecordset(MySql, dbOpenSnapshot)
If Rst.RecordCount > 0 Then
Rst.MoveLast
Rst.MoveFirst
Do While Not Rst.EOF
If Nz(Concatenate, "") = "" Then
Concatenate = Rst.Fields(FieldConct)
Else
Concatenate = Concatenate & ", " & Rst.Fields(FieldConct)
End If
Rst.MoveNext
Loop
End If
Rst.Close
Set Rst = Nothing
End Function
I hope this makes sense. If anyone has any ideas I would be glad to hear them, and/or explain anything i missed a bit clearer
I attached the sample database again, so you can see how its supposed to work, but doesnt when applied to my database. I am getting a 3464 error....