CODEWITHSUNDEEP
sqlvbams-access
reickmey
reickmey

Reputation: 65

Concatenate multiple fields where some fields are empty

I have a table named tblFriends, see below:

enter image description here

I would like to concatenate the first row, but the number of columns fluctuates daily.

Lets say that the fields names are 1, 2, 3, 4...30 (where 30 is the max number of fields possible.)

How do I concatenate all the string text in these fields? Also, will it cause an error for empty fields? For example if, as the table shows, there are only 4 fields with records in them will it give me an error if I still try and concat all 30 fields?

This is the code I was planning on using but it doesn't seem to work:

Dim strSQLCat As String

strSQLCat = "UPDATE tblFriends SET 31 = CONCAT( 1 & ',' & 2 & ',' & 3 & '....' & 30) Where Record = 1;"

DoCmd.RunSQL strSQLCat

Upvotes: 1

Views: 338

Answers (1)

June7
June7

Reputation: 21379

Because field names are only numbers, enclose in [ ]:

CurrentDb.Execute "UPDATE tblFriends SET [31] = [1] & ',' & [2] & ',' & [3] & . . ."

Advise not to save this calculated value to table and instead just calculate in query when needed. SELECT tblFriends.*, [1] & "," & [2] & "," & [3] & . . . AS [31] FROM tblFriends;

Upvotes: 1

Related Questions