Combining Multiple Rows of one Field into One Result

[jackiefm]

I realize the thread I am responding to was posted in January but I am basically having the same issue. I am not familiar with VBA but use Access daily. I have written simple scripts but nothing to write home about. I followed the previous thread and my situation is very similar.

I have an application with users. These users have access to multiple terminals. I am trying to combine the TerminalName field with all terminals the user has access to:

User Terminals
John Doe 121,65,307
Jane Doe 121

etc.

I pasted the module:

Option Compare Database
'Concat Returns Products
'listed by company
Public Function Concat(strPartyName As String, _
                       strTerminalName As String) As String
    Static strLastPartyName As String
    Static strTerminals As String
 
    If strPartyName = strLastPartyName Then
        strTerminals = strTerminals & ", " & strTerminalName
    Else
        strLastPartyName = strPartyName
        strTerminals = strTerminalName
    End If
    Concat = strTerminals
End Function
 

and my sql query is:

SELECT [Assigned Terminals Export].PartyName, [Assigned Terminals Export].TerminalName, Last(Concat([PartyName],[TerminalName])) AS Terminals
FROM [Assigned Terminals Export]
GROUP BY [Assigned Terminals Export].PartyName, [Assigned Terminals Export].TerminalName;
 

When I try to run the query I get:
Undefined function 'Concat' in expression.

Can you please help me?

[Scott Price]

Link to previous thread: http://www.thescripts.com/forum/showthread.php?t=589999

JackieFM:

You have asked your question in another member's thread. This is called thread hijacking and is not allowed on this site. There are various reasons for this, the principal of which is that it typically diverts the flow of the thread away from his needs and into yours. Another is that it will tend to mean that your new question is not seen as such, and may get overlooked by many of our experts (You appreciate that they're not looking for answered questions).
Please post your questions in their own threads in future (See How to Ask a Question ).

I have split the thread into two, and your question now has it's own thread.

MODERATOR.

[Scott Price]

The function code needs to go into a standard code module. Create one in the vba editor window by clicking on the Insert Menu, then Module.

This should help!

Regards, and welcome to the Scripts.

Scott

[jackiefm]

I have added it as a module but it still gives me the same error. Is there something else I am supposed to do?

[DThreadgill]

I realize the thread I am responding to was posted in January but I am basically having the same issue. I am not familiar with VBA but use Access daily. I have written simple scripts but nothing to write home about. I followed the previous thread and my situation is very similar.

I have an application with users. These users have access to multiple terminals. I am trying to combine the TerminalName field with all terminals the user has access to:

User Terminals
John Doe 121,65,307
Jane Doe 121

etc.

I pasted the module:

Option Compare Database
'Concat Returns Products
'listed by company
Public Function Concat(strPartyName As String, _
                       strTerminalName As String) As String
    Static strLastPartyName As String
    Static strTerminals As String
 
    If strPartyName = strLastPartyName Then
        strTerminals = strTerminals & ", " & strTerminalName
    Else
        strLastPartyName = strPartyName
        strTerminals = strTerminalName
    End If
    Concat = strTerminals
End Function
 

and my sql query is:

SELECT [Assigned Terminals Export].PartyName, [Assigned Terminals Export].TerminalName, Last(Concat([PartyName],[TerminalName])) AS Terminals
FROM [Assigned Terminals Export]
GROUP BY [Assigned Terminals Export].PartyName, [Assigned Terminals Export].TerminalName;
 

When I try to run the query I get:
Undefined function 'Concat' in expression.

Can you please help me?

In line 10 of your code:
strTerminals = strTerminals & ", " & strTerminalName
I take it the message comes up and it doesn't like the comma (which is in red)? It may be looking for a replace function when you use a comma. Try a space or a semicolon...

[jackiefm]

Unfortunately that did not help. Is there a reference I need to add maybe? I currently have:

Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.5 Library

[Scott Price]

The error message: "Undefined function Concat" is indicating that you haven't properly placed the function! For some reason, vba hasn't yet recognized that this function does indeed exist.

Are you sure that you placed this into a Module, and not a Class Module?
Have you also done a Debug>Compile, and Save?

Regards,
Scott

[Scott Price]

I just copied and pasted your code into a fresh code module, clicked the Debug menu, Compile, then Save.

Tried it in a fresh query in my test database and it works just fine.

Your references look fine to me as well. If you still are having problems, we'll have to look a little bit closer at them, but otherwise...

Regards,
Scott

[jackiefm]

I can only apologize for the missing the most obvious. I neglected to remove the underscore in the module after I pasted it. Once I removed the underscore, complied and saved it worked like a charm.

Thank you, Thank you

[jackiefm]

I do however have one other problem. The field size is limited to 255 char. Can that be changed. Each terminal is 3 characters long, with the space and comma I am limited to 51 terminals can be combined to this field. Some users have access to over 700 terminals.

[Scott Price]

I do however have one other problem. The field size is limited to 255 char. Can that be changed. Each terminal is 3 characters long, with the space and comma I am limited to 51 terminals can be combined to this field. Some users have access to over 700 terminals.

Try changing the table field specification to Memo data type. The standard Text data type restricts to the 255 characters like you mention.

Glad you got it working!

Regards,
Scott

[jackiefm]

I believe it is using the default text format which is limited to the 255 char. It is a new field created through the query. Is there a way to change it in the sql? The field is:

Last(Concat([PartyName],[TerminalName])) AS Terminals

This field was created in the query. I have changed both TerminalName and PartyName to Memo fields with no success.

[Scott Price]

Hi Jackie,

Apparently you are suffering from the aggregate function Group By and probably Last() in your query. Have a look at this link to Allen Browne's tip sheet about Truncation of Memo Fields

Regards,
Scott

[jackiefm]

Unfortunately I followed all "rules" to prevent this issue but still seem to be unable to resolve it. Below is my query. There is not formatting in query or table, no grouping on TerminalName, any suggestions?

SELECT [Assigned Terminals Export].PartyID, [Assigned Terminals Export].PartyName, Last(Concat([PartyName],[TerminalName])) AS Terminals
FROM [Assigned Terminals Export]
GROUP BY [Assigned Terminals Export].PartyID, [Assigned Terminals Export].PartyName
ORDER BY [Assigned Terminals Export].PartyName;

[Scott Price]

I would strip it down to the basic Concat() function query, then use that to update a table. From there you can run it through another query to do the grouping.

In other words:

SELECT [Assigned Terminals Export].PartyID, [Assigned Terminals Export].PartyName, Last(Concat([PartyName],[TerminalName])) AS Terminals
FROM [Assigned Terminals Export];

Regards,
Scott