I want to concatenate the following with a space between each:
All:concat([expr1],[Expr2],[Expr3],[Expr4],[Expr5],[Expr6],[Expr7],[Expr8]," ")
It is saying that I have the expression you entered has a function containing the wrong number of arguments.
Option Compare Database
' Concatenation of field values in recordset (=existing table or query) with optional where-condition imposed
' Concatenation of field values in recordset (=existing table or query) with optional where-condition imposed
' The function uses a DAO recordset, so parameter queries and Access-specific features will cause an error
' (examples: references to controls on forms, user-defined functions and Access functions like Nz)
' Example usage: create a text box with control source
' =Concat("Persons","Surname")
' or
' =Concat("Persons","Surname","Gender='M'","Age","/")
Code: Select all
Function Concat _
(aRSet As String, _
aField As String, _
Optional aCondition As String, _
Optional aOrderField As String, _
Optional aSeparator As String) As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strRes As String
If aCondition <> "" Then
strSQL = " AND (" & aCondition & ")"
End If
If aOrderField = "" Then
aOrderField = aField
End If
If aSeparator = "" Then
aSeparator = ", "
End If
If strSQL <> "" Then
strSQL = " WHERE" & Mid$(strSQL, 5)
End If
strSQL = "SELECT [" & aField & "] FROM [" & aRSet & "]" & _
strSQL & " ORDER BY [" & aOrderField & "];"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
While Not rst.EOF
strRes = strRes & aSeparator & rst(aField)
rst.MoveNext
Wend
If strRes <> "" Then
strRes = Mid$(strRes, Len(aSeparator) + 1)
End If
Concat = strRes
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Function