By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,493 Members | 3,101 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,493 IT Pros & Developers. It's quick & easy.

Combining Multiple Rows of one Field into One Result

P: 15
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. 'Concat Returns Products
  3. 'listed by company
  4. Public Function Concat(strPartyName As String, _
  5.                        strTerminalName As String) As String
  6.     Static strLastPartyName As String
  7.     Static strTerminals As String
  8.  
  9.     If strPartyName = strLastPartyName Then
  10.         strTerminals = strTerminals & ", " & strTerminalName
  11.     Else
  12.         strLastPartyName = strPartyName
  13.         strTerminals = strTerminalName
  14.     End If
  15.     Concat = strTerminals
  16. End Function
  17.  
and my sql query is:

Expand|Select|Wrap|Line Numbers
  1. SELECT [Assigned Terminals Export].PartyName, [Assigned Terminals Export].TerminalName, Last(Concat([PartyName],[TerminalName])) AS Terminals
  2. FROM [Assigned Terminals Export]
  3. GROUP BY [Assigned Terminals Export].PartyName, [Assigned Terminals Export].TerminalName;
  4.  
When I try to run the query I get:
Undefined function 'Concat' in expression.

Can you please help me?
Oct 3 '07 #1
Share this Question
Share on Google+
14 Replies


Scott Price
Expert 100+
P: 1,384
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.
Oct 3 '07 #2

Scott Price
Expert 100+
P: 1,384
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
Oct 3 '07 #3

P: 15
I have added it as a module but it still gives me the same error. Is there something else I am supposed to do?
Oct 3 '07 #4

P: 57
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. 'Concat Returns Products
  3. 'listed by company
  4. Public Function Concat(strPartyName As String, _
  5.                        strTerminalName As String) As String
  6.     Static strLastPartyName As String
  7.     Static strTerminals As String
  8.  
  9.     If strPartyName = strLastPartyName Then
  10.         strTerminals = strTerminals & ", " & strTerminalName
  11.     Else
  12.         strLastPartyName = strPartyName
  13.         strTerminals = strTerminalName
  14.     End If
  15.     Concat = strTerminals
  16. End Function
  17.  
and my sql query is:

Expand|Select|Wrap|Line Numbers
  1. SELECT [Assigned Terminals Export].PartyName, [Assigned Terminals Export].TerminalName, Last(Concat([PartyName],[TerminalName])) AS Terminals
  2. FROM [Assigned Terminals Export]
  3. GROUP BY [Assigned Terminals Export].PartyName, [Assigned Terminals Export].TerminalName;
  4.  
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...
Oct 3 '07 #5

P: 15
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
Oct 3 '07 #6

Scott Price
Expert 100+
P: 1,384
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
Oct 3 '07 #7

Scott Price
Expert 100+
P: 1,384
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
Oct 3 '07 #8

P: 15
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
Oct 3 '07 #9

P: 15
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.
Oct 3 '07 #10

Scott Price
Expert 100+
P: 1,384
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
Oct 3 '07 #11

P: 15
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.
Oct 4 '07 #12

Scott Price
Expert 100+
P: 1,384
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
Oct 4 '07 #13

P: 15
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;
Oct 4 '07 #14

Scott Price
Expert 100+
P: 1,384
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT [Assigned Terminals Export].PartyID, [Assigned Terminals Export].PartyName, Last(Concat([PartyName],[TerminalName])) AS Terminals
  2. FROM [Assigned Terminals Export];
Regards,
Scott
Oct 4 '07 #15

Post your reply

Sign in to post your reply or Sign up for a free account.