Concat Equivalent in Ms Access

CONCAT equivalent in MS Access

UPDATE myTable
SET [My Column] = "Prefix " & [My Column] 
WHERE [Different Column]='someValue';

As far as I am aware there is no CONCAT

undefined function 'concat' vb.net

MS Access doesn't have a function CONCAT. Instead, you must use a string concatenation:

Public Function getStudents(ByVal oStudentsBO As StudentsBO) As DataTable
    dt = New DataTable
    sql = "SELECT [KY_ID_NUM] as [ID Num], [NM_NICK] as [Nick Name], [CD_AGE_GROUP] as [Age Group], " &
          "[NM_LAST] as [Last Name], [NM_FIRST] as [First Name], " &
          " [NM_GFIRST] & [NM_GLAST] FROM [tblStudents] " &
          "WHERE [NM_FIRST] Like '%" & oStudentsBO.NM_FIRST & "%'" &
          "OR [NM_NICK] LIKE '%" & oStudentsBO.NM_NICK & "%'" &
          "OR [NM_LAST] Like '%" & oStudentsBO.NM_LAST & "%'"
    dt = oDA.getDataTable(sql)
    Return dt
End Function

Problem with string concatenation in Microsoft Access SQL query

You can implement the equivalent of concat_ws() in MS Access using nz(), ltrim() and conditional logic:

select Switch(LocationType = 1,
              ltrim(nz(" " + CStr(HouseNumber), "") +
                    nz(" " + HouseNumberSuffix, "") + 
                    nz(" " + PrefixDirectional, "") +
                    nz(" " + StreetName, "") +
                    nz(" " + StreetType, "") + ", " &
                    nz(" " + Qualifier, "") + ", "
                    nz(" " + VenueName, "")
                   ),
              . . .
              )

Undefined function 'Concat' in expression. for Bartender SQL Statement

The concat() operator in MS Access is &:

UPDATE [Sheet1$]
    SET [Outer Barcode] = '0' & [Outer Barcode];

is there a group_concat function in ms-access?

You should ask yourself if you need a generic solution (another is by Allen Browne) or if you need it just for the present purpose. If you really only need it this once, do it the easy way.

On a side note, when concatenating lists in VBA code, take advantage of a trick taught to me by long-time Access guru Trevor Best, and that's to stick the delimiter at the beginning of every value and then use Mid() to strip it off. Instead of this inside your loop through the child records:

  If Len(strOutput) = 0 Then
     strOutput = NewValue
  Else
     strOutput = strOutput & ", " & NewValue
  End If

...use this inside the loop:

  strOutput = strOutput & ", " & NewValue

...and then when you exit the loop, strip off the leading delimiter:

  strOutput = Mid(strOutput, 3)

This has implications all over the place and simplifies code for concatenation in a whole host of contexts.

Concatenate records and GROUP BY in Access

There is no Group_Concat in Access :/. Probably there is no solution that excludes VBA.

Here is one possible: Concatenating Rows through a query

How to do: Reports with concatenated text in fields organized by a key

This will require VBA code. One commonly used function provided by Allen Browne at http://allenbrowne.com/func-concat.html

Assuming there is a Titles table where the ID is unique primary key associated with FK in sample table - use that table as source for query then call the function 4 times (also assuming there are only 4 types).

SELECT TitleID, "Title_" & TitleID AS Title, 
ConcatRelated("'Sys_' & [sysID]", "sampleTable", "FK=" & [TitleID] & " AND Type=1", "sysID") AS Type1, 
ConcatRelated("'Sys_' & [sysID]", "sampleTable", "FK=" & [TitleID] & " AND Type=2", "sysID") AS Type2,
ConcatRelated("'Sys_' & [sysID]", "sampleTable", "FK=" & [TitleID] & " AND Type=3", "sysID") AS Type3,
ConcatRelated("'Sys_' & [sysID]", "sampleTable", "FK=" & [TitleID] & " AND Type=4", "sysID") AS Type4
FROM Titles;

Alternative is CROSSTAB (again assuming only 4 types):

TRANSFORM Max(ConcatRelated("'Sys_' & [sysID]","sampleTable","FK=" & [FK] & " AND Type=" & [Type], "sysID")) AS SysType
SELECT [FK], "Title_" & [FK] AS Title
FROM sampleTable
GROUP BY [FK], "Title_" & [FK] 
PIVOT [Type] IN (1,2,3,4);

Be aware performance can be slow with large dataset. Both appear to have same performance with sample data.

No subreports needed. However, building a stable report based on CROSSTAB where columns returned are dynamic can be a challenge.

ConcatRelated ms access

Assuming this is Allen Browne's ConcatRelated function, the field name and table name must be passed as strings, that means wrapping in quote marks. Also need AND operator for the criteria argument.

SELECT FailureOver90Days.RMA, FailureOver90Days.Shop_Order, FailureOver90Days.[SN Received], FailureOver90Days.record_time, 
ConcatRelated("[FD Code]","[FailureOver90Days]","RMA = " & [RMA] & " AND Shop_Order = " & [Shop_Order] & " AND [SN Received] ='" & [SN Received] & "'") AS [FD Codes]
FROM FailureOver90Days
GROUP BY RMA, Shop_Order, [SN Received], record_time, 
ConcatRelated("[FD Code]","[FailureOver90Days]","RMA = " & [RMA] & " AND Shop_Order = " & [Shop_Order] & " AND [SN Received] ='" & [SN Received] & "'")
HAVING (((FailureOver90Days.RMA) Is Not Null) AND ((FailureOver90Days.Shop_Order) Is Not Null) AND ((FailureOver90Days.[SN Received]) Is Not Null) AND ((FailureOver90Days.record_time) Between #1/2/2020# And #1/9/2020#));

---

Related Topics