Combine two fields in subform

[jmh via AccessMonster.com]

I'm trying to combine the field [ProductsSold] which is in a datasheet.
Here's my query statement. But I get an error saying Undefined field
"Concatenate" in expression. Can you see where I am going wrong?

Productsx: Concatenate("SELECT ProductsSold FROM tblProducts WHERE Client ="
& [Client])


Thanks in advance.

 

[jmh via AccessMonster.com]

Let me explain more.

ProductsSold can have a product listed on each row. For example:
grapes
oranges
apples

I am trying to have them listed as grapes, oranges, apples.

I'm trying to combine the field [ProductsSold] which is in a datasheet.
Here's my query statement. But I get an error saying Undefined field
"Concatenate" in expression. Can you see where I am going wrong?

Productsx: Concatenate("SELECT ProductsSold FROM tblProducts WHERE Client ="
& [Client])

Thanks in advance.

 

[Guest]

In query design view enter this in the grid row FIELD --
Productsx: [ProductsSold] &" - " & [Client]

You did not say what the [Client] must be equal to. But if you want it to
prompt you then put this in the criteria row under where you put [Client]
alone --
[Enter Client] -- OR --
Like [Enter Client] & "*"
to be able to enter just the start of the client.

Let me explain more.

ProductsSold can have a product listed on each row. For example:
grapes
oranges
apples

I am trying to have them listed as grapes, oranges, apples.

I'm trying to combine the field [ProductsSold] which is in a datasheet.
Here's my query statement. But I get an error saying Undefined field
"Concatenate" in expression. Can you see where I am going wrong?

Productsx: Concatenate("SELECT ProductsSold FROM tblProducts WHERE Client ="
& [Client])

Thanks in advance.

 

[Douglas J Steele]

There's no function named Concatenate built into Access, so I have to assume
it's a user-defined function. Does it exist in your application? If so,
where was it defined: in a stand-alone module, or in a module associated
with a form? It should be in a stand-alone module, and cannot be defined as
Private.

 

[jmh via AccessMonster.com]

Ah ha. I see. There was a free download from Duane Hookum that I was trying
to get my answer from. Concatenante was defined in a module.

But when I copy this code intact and put it in a module, I still get errors.

***
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

There's no function named Concatenate built into Access, so I have to assume
it's a user-defined function. Does it exist in your application? If so,
where was it defined: in a stand-alone module, or in a module associated
with a form? It should be in a stand-alone module, and cannot be defined as
Private.

I'm trying to combine the field [ProductsSold] which is in a datasheet.
Here's my query statement. But I get an error saying Undefined field

[quoted text clipped - 4 lines]

Thanks in advance.

 

[Douglas J Steele]

What kind of errors?

What did you name the module? Modules cannot be named the same as functions
or subroutines, so if you named it Concatenate, rename it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ah ha. I see. There was a free download from Duane Hookum that I was trying
to get my answer from. Concatenante was defined in a module.

But when I copy this code intact and put it in a module, I still get errors.

***
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

There's no function named Concatenate built into Access, so I have to assume
it's a user-defined function. Does it exist in your application? If so,
where was it defined: in a stand-alone module, or in a module associated
with a form? It should be in a stand-alone module, and cannot be defined as
Private.

I'm trying to combine the field [ProductsSold] which is in a datasheet.
Here's my query statement. But I get an error saying Undefined field

[quoted text clipped - 4 lines]

Thanks in advance.