Combine two fields in subform

  • Thread starter Thread starter jmh via AccessMonster.com
  • Start date Start date
J

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.
 
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.
Click to expand...
 
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.


jmh via AccessMonster.com said:
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.
Click to expand...
Click to expand...
 
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.
 
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
Click to expand...
[quoted text clipped - 4 lines]
Thanks in advance.
Click to expand...
Click to expand...
 
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!)


jmh via AccessMonster.com said:
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
Click to expand...
[quoted text clipped - 4 lines]
Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top