Concetenate a field value with the same field in different rows

[JonoGee]

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)

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

[Welshgasman]

No idea as to what a 3464 error is.
Try putting your sql string into a string variable.
Then you can debug.print it until you get it correct, then use in the openrecordset command.
If you still cannot see the error, you can post the output back here.

[orange]

?accesserror(3464)
Data type mismatch in criteria expression.

What is GetKey?

[pbaldy]

tblCustomField referred to in the query is not included in the sample, nor is the table in your picture. The other query using the function runs fine.

[June7]

Provide the exact error message, not just the number.

Prefixing field with table name is not necessary in this case. Parens are not needed. If table or field names include space or special characters or are same as reserved word, use [], use them anyway just to be safe. If key is autonumber/number, don't use apostrophe delimiters. Semi-colon is optional.

MySql = "SELECT * FROM [" & Gettbl & "] WHERE [" & KeyValue & "]=" & GetKey

Using the same field as input to two arguments does not make sense to me. Notice in the example db it uses two fields with same name but they are in different tables. This is why I advise not to use exact same name in multiple tables.

Why not provide your db where this code fails?

[Gicu]

Have a look at the modified sample attached; the original one assumed the GetKey was a string so in the function it was enclosed in single quotes. I copied the function and modified it to accept a numeric GetKey (new function Concatenate_Numeric). Try to use this one with the same arguments you had in the original.

I have a similar function on my site (JoinFromArray @ http://forestbyte.com/vba-code-samples/) but lately I've been using theDbGuy's SimpleCSV():
http://www.accessmvp.com/thedbguy/co...itle=simplecsv

Just add the function to a standard module and in your case usage would be:

CustomFields:SimpleCSV("SELECT [CustomField] FROM 
tblCustomFieldsSKU WHERE EMGSKUID=" & [EMGSKUID], ",")

Cheers,