Sum of text fields?

[Guest]

I have one table, Customers and one table called Emails.
Each customer can have several emails, and these two tables are linked with
the field CustomerID. For example:

CustomerID EmailID Email
1 1 (e-mail address removed)
1 2 (e-mail address removed)
2 3 (e-mail address removed)
2 4 (e-mail address removed)
3 5 (e-mail address removed)

I would like to make a query that adds all emails for one customer into one
expression field, separated by commas. Like this:

CustomerID Expression
1 (e-mail address removed), (e-mail address removed)
2 (e-mail address removed), (e-mail address removed)
3 (e-mail address removed)

Can anyone help me with this?

Thanks in advance!
/Anders

 

[Allen Browne]

See:
Return a concatenated list of sub-record values
at:
http://www.mvps.org/access/modules/mdl0004.htm

 

[Guest]

Thanks Allen!
It worked perfectly in my query. Now I am trying to use your function in the
following code:

Dim Test
Set Test = [fConcatChild("Email";"CustomerID";"Email";"Long";[CustomerID])]

When I do this, I get the error message nr 2465, that it's unable to find
the field "|" in the expression.

Can you help me?
Thanks!
/Anders




"Allen Browne" skrev:

 

[Allen Browne]

Drop the "Set". It's used only for object variables.

The function returns a string, so it would be a good idea to declare a
string, i.e.:
Dim Test As String
Otherwise Test is untyped (a variant), and so the compiler did not tell you
that you should not be using Set with it.

 

[John Vinson]

Set Test = [fConcatChild("Email";"CustomerID";"Email";"Long";[CustomerID])]

When I do this, I get the error message nr 2465, that it's unable to find
the field "|" in the expression.

Remove the square brackets.

 

[Guest]

I dropped the "Set". Now it looks like this:

Dim Test As String
Test = [fConcatChild("Email";"CustomerID";"Email";"Long";[CustomerID])]

However, I still get the same error message as before.
Thanks
/Anders



"Allen Browne" skrev:

Drop the "Set". It's used only for object variables.

The function returns a string, so it would be a good idea to declare a
string, i.e.:
Dim Test As String
Otherwise Test is untyped (a variant), and so the compiler did not tell you
that you should not be using Set with it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen!
It worked perfectly in my query. Now I am trying to use your function in
the
following code:

Dim Test
Set Test =
[fConcatChild("Email";"CustomerID";"Email";"Long";[CustomerID])]

When I do this, I get the error message nr 2465, that it's unable to find
the field "|" in the expression.

Can you help me?
Thanks!
/Anders

 

[Guest]

I removed the square-brackets. Now I get a compilation-error (when trying to
leave this line).

Set Test = fConcatChild("Email";"CustomerID";"Email";"Long";[CustomerID])



"John Vinson" skrev:

Set Test = [fConcatChild("Email";"CustomerID";"Email";"Long";[CustomerID])]

When I do this, I get the error message nr 2465, that it's unable to find
the field "|" in the expression.

Remove the square brackets.

 

[Guest]

I removed the square-brackets. Now I get a compilation-error (when trying to
leave the line).

Set Test = fConcatChild("Email";"CustomerID";"Email";"Long";[CustomerID])

^ this first semicolon is
highlighted and the error
message says something (in my language, Swedish) with means something like
there is a missing separator or ")".


"John Vinson" skrev:

Set Test = [fConcatChild("Email";"CustomerID";"Email";"Long";[CustomerID])]

When I do this, I get the error message nr 2465, that it's unable to find
the field "|" in the expression.

Remove the square brackets.

 

[Allen Browne]

Anders, try commas as separators between the arguments instead of
semicolons.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I removed the square-brackets. Now I get a compilation-error (when trying
to
leave the line).

Set Test = fConcatChild("Email";"CustomerID";"Email";"Long";[CustomerID])

^ this first semicolon is
highlighted and the error
message says something (in my language, Swedish) with means something like
there is a missing separator or ")".


"John Vinson" skrev:

Set Test =
[fConcatChild("Email";"CustomerID";"Email";"Long";[CustomerID])]

When I do this, I get the error message nr 2465, that it's unable to
find
the field "|" in the expression.

Remove the square brackets.

 

[Guest]

Ok, now I i can leave the line. However, I still get the same error message
when I run the code: 2465, unable to find the field "|" in the expression.


"Allen Browne" skrev:

Anders, try commas as separators between the arguments instead of
semicolons.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I removed the square-brackets. Now I get a compilation-error (when trying
to
leave the line).

Set Test = fConcatChild("Email";"CustomerID";"Email";"Long";[CustomerID])

^ this first semicolon is
highlighted and the error
message says something (in my language, Swedish) with means something like
there is a missing separator or ")".


"John Vinson" skrev:

On Thu, 19 Aug 2004 22:57:01 -0700, "Anders Berlin"

Set Test =
[fConcatChild("Email";"CustomerID";"Email";"Long";[CustomerID])]

When I do this, I get the error message nr 2465, that it's unable to
find
the field "|" in the expression.

Remove the square brackets.

 

[Allen Browne]

The pipe character is a place holder; Access should substitute the name of
the field that it cannot find. The fact that it can't suggests possibilities
such as:
- corrupted database;
- Name AutoCorrect error;
- completely malformed argument.

Check that the field and table names are correct, and that CustomerID has a
value to pass in.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ok, now I i can leave the line. However, I still get the same error
message
when I run the code: 2465, unable to find the field "|" in the expression.


"Allen Browne" skrev:

Anders, try commas as separators between the arguments instead of
semicolons.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I removed the square-brackets. Now I get a compilation-error (when
trying
to
leave the line).

Set Test =
fConcatChild("Email";"CustomerID";"Email";"Long";[CustomerID])

^ this first semicolon is
highlighted and the error
message says something (in my language, Swedish) with means something
like
there is a missing separator or ")".


"John Vinson" skrev:

On Thu, 19 Aug 2004 22:57:01 -0700, "Anders Berlin"

Set Test =
[fConcatChild("Email";"CustomerID";"Email";"Long";[CustomerID])]

When I do this, I get the error message nr 2465, that it's unable to
find
the field "|" in the expression.

Remove the square brackets.