Concatenate Error

[Wynn]

I am trying to create a form with a concatenated field to solve a one-to-many
relationship as explained in this example.
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'
The example works great, but I can't duplicate it. I've made (what I think
is) an identical database, but I get an error in the field that should be
concatenated.
I've tried to do the "example of first names in the family query" as well,
and I get an error that the function "Concatenate" is not defined in the
expression.

 

[Sylvain Lafontaine]

If the Concatenate function is defined in VBA but it's called in the
SQL-Statement, then SQL-Server won't see it because the server doesn't see
what's on the client machine.

You can write functions directly in SQL but the syntax is different.

There is also other ways to solve this kind of problems in SQL.

 

[Wynn]

What should I do?

If the Concatenate function is defined in VBA but it's called in the
SQL-Statement, then SQL-Server won't see it because the server doesn't see
what's on the client machine.

You can write functions directly in SQL but the syntax is different.

There is also other ways to solve this kind of problems in SQL.

 

[giorgio rancati]

What should I do?

Hi Wynn,

create a new T-SQL function
----
CREATE FUNCTION Concatenate
(@FamId int,@Delim Char(1))
RETURNS Varchar(8000) AS
BEGIN
DECLARE @Ret Varchar(8000)
SET @Ret=''

SELECT @Ret=@Ret+@Delim+FirstName
FROM dbo.tblFamMem
WHERE FamId=@FamId
ORDER BY FamMemID

SET @Ret=STUFF(@Ret,1,1,'')
RETURN @Ret
END
----

then you use it in a query
----
SELECT *,dbo.Concatenate(FamID,',') AS Members
FROM dbo.tblFamily
----

Result

FamID famLastName Members
----- ----------- ----------------------------------------------
1 Hookom Duane,Laura,Jake,Chelsey,Dakota,Josie,Jackson
2 LeMier Royal,Janice,Kristina,Jenna,Kaytlin,Heidi
3 Young Skip,Mary Kay,Nathan,Katherine,Rebecca,Dude


bye

 

[Wynn]

Thank you giorgi. I'll try that out. I figured out the problem - hadn´t
copied the VBA code correctly.

"giorgio rancati" escribió: