ms access comma separator

[Guest]

I have created a query in ms access with the following fields: date, course
description, course convenor, staff member. (Staff Member is generated from
another table as follows: Staff Member: [Completed Training]![First Name] &
" " & [Completed Training]!Surname.

From this query I have generated a main report and have included 2
subreports. Everything is working ok except I want my names to appear as:

Julie Evans, Vicki Gunner, Barry Bartholomew, Peter O'Brien (and not)

Julie Evans
Vicki Gunner
Barry Bartholomew
Peter O'Brien

I don't know if i suffer from dumb blonde syndrome but I cannot make heads
nor tales of any of the help suggestions listed. Could someone please
explain how to do this in easy to follow instructions. Any help would be
very much appreciated.

 

[Duane Hookom]

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Guest]

I have created a query with the following Field, but when I run the query it
comes up with "Undefined Function 'Concatenate' in expression" - ?

Employee: Concatenate("SELECT Staff member FROM qry Proposed Training by
Date Selection WHERE Date =" & [Proposed Training by Date Selection.Date])

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP

I have created a query in ms access with the following fields: date,
course
description, course convenor, staff member. (Staff Member is generated
from
another table as follows: Staff Member: [Completed Training]![First Name]
&
" " & [Completed Training]!Surname.

From this query I have generated a main report and have included 2
subreports. Everything is working ok except I want my names to appear as:

Julie Evans, Vicki Gunner, Barry Bartholomew, Peter O'Brien (and not)

Julie Evans
Vicki Gunner
Barry Bartholomew
Peter O'Brien

I don't know if i suffer from dumb blonde syndrome but I cannot make heads
nor tales of any of the help suggestions listed. Could someone please
explain how to do this in easy to follow instructions. Any help would be
very much appreciated.

 

[John Spencer (MVP)]

Did you copy and paste the function Concatenate into a module and then save the
module with some name other than Concatenate?

Is the function really named "Concatenate" or does it have a different name?

Your query needs to be rewritten. Any field, query, or table name with spaces
must be surrounded by brackets []. When you embed a date string you must
surround it with # marks to tell Access that this is a date. The word Date is a
reserved word in Access and you should if possible rename the Date field to
something a bit more descriptive like TrainingDate.


Employees: Concatenate("SELECT [Staff member] FROM [qry Proposed Training by
Date Selection] WHERE [Date] =#" & [Proposed Training by Date Selection].[Date]
& "#")

Hope this all helps.

I have created a query with the following Field, but when I run the query it
comes up with "Undefined Function 'Concatenate' in expression" - ?

Employee: Concatenate("SELECT Staff member FROM qry Proposed Training by
Date Selection WHERE Date =" & [Proposed Training by Date Selection.Date])

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP

I have created a query in ms access with the following fields: date,
course
description, course convenor, staff member. (Staff Member is generated
from
another table as follows: Staff Member: [Completed Training]![First Name]
&
" " & [Completed Training]!Surname.

From this query I have generated a main report and have included 2
subreports. Everything is working ok except I want my names to appear as:

Julie Evans, Vicki Gunner, Barry Bartholomew, Peter O'Brien (and not)

Julie Evans
Vicki Gunner
Barry Bartholomew
Peter O'Brien

I don't know if i suffer from dumb blonde syndrome but I cannot make heads
nor tales of any of the help suggestions listed. Could someone please
explain how to do this in easy to follow instructions. Any help would be
very much appreciated.

 

[Guest]

John, Thanks for your help - I'm a temp and have designed this excellent
Training Database using MS Access (proud of how good it really is) - the only
thing I need to do is to display the names across the page but unfortunately
I cannot get my head around the Concatenate function, modules, and how they
all come together(frustrating because I tackled really hard reports and
queries without dramas). I could waste your time all day on this but I won't
because I've lost total confidence and think I should go and do an advanced
course - justed wanted to thank you for your help - I have used this
discussion group many times and would be lost without it. - MissV

Did you copy and paste the function Concatenate into a module and then save the
module with some name other than Concatenate?

Is the function really named "Concatenate" or does it have a different name?

Your query needs to be rewritten. Any field, query, or table name with spaces
must be surrounded by brackets []. When you embed a date string you must
surround it with # marks to tell Access that this is a date. The word Date is a
reserved word in Access and you should if possible rename the Date field to
something a bit more descriptive like TrainingDate.


Employees: Concatenate("SELECT [Staff member] FROM [qry Proposed Training by
Date Selection] WHERE [Date] =#" & [Proposed Training by Date Selection].[Date]
& "#")

Hope this all helps.

I have created a query with the following Field, but when I run the query it
comes up with "Undefined Function 'Concatenate' in expression" - ?

Employee: Concatenate("SELECT Staff member FROM qry Proposed Training by
Date Selection WHERE Date =" & [Proposed Training by Date Selection.Date])

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP


I have created a query in ms access with the following fields: date,
course
description, course convenor, staff member. (Staff Member is generated
from
another table as follows: Staff Member: [Completed Training]![First Name]
&
" " & [Completed Training]!Surname.

From this query I have generated a main report and have included 2
subreports. Everything is working ok except I want my names to appear as:

Julie Evans, Vicki Gunner, Barry Bartholomew, Peter O'Brien (and not)

Julie Evans
Vicki Gunner
Barry Bartholomew
Peter O'Brien

I don't know if i suffer from dumb blonde syndrome but I cannot make heads
nor tales of any of the help suggestions listed. Could someone please
explain how to do this in easy to follow instructions. Any help would be
very much appreciated.

 

[Duane Hookom]

MissV,
You can import the module containing the function into your MDB file. This
is done the same as importing tables or other objects File->Get External
Data->Import.
You can then browse to find my sample database and import the module. This
will allow you to use the Concatenate() function like you would use any
other built-in function.

--
Duane Hookom
MS Access MVP

John, Thanks for your help - I'm a temp and have designed this excellent
Training Database using MS Access (proud of how good it really is) - the
only
thing I need to do is to display the names across the page but
unfortunately
I cannot get my head around the Concatenate function, modules, and how
they
all come together(frustrating because I tackled really hard reports and
queries without dramas). I could waste your time all day on this but I
won't
because I've lost total confidence and think I should go and do an
advanced
course - justed wanted to thank you for your help - I have used this
discussion group many times and would be lost without it. - MissV

Did you copy and paste the function Concatenate into a module and then
save the
module with some name other than Concatenate?

Is the function really named "Concatenate" or does it have a different
name?

Your query needs to be rewritten. Any field, query, or table name with
spaces
must be surrounded by brackets []. When you embed a date string you must
surround it with # marks to tell Access that this is a date. The word
Date is a
reserved word in Access and you should if possible rename the Date field
to
something a bit more descriptive like TrainingDate.


Employees: Concatenate("SELECT [Staff member] FROM [qry Proposed Training
by
Date Selection] WHERE [Date] =#" & [Proposed Training by Date
Selection].[Date]
& "#")

Hope this all helps.

I have created a query with the following Field, but when I run the
query it
comes up with "Undefined Function 'Concatenate' in expression" - ?

Employee: Concatenate("SELECT Staff member FROM qry Proposed Training
by
Date Selection WHERE Date =" & [Proposed Training by Date
Selection.Date])

:

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP


I have created a query in ms access with the following fields:
date,
course
description, course convenor, staff member. (Staff Member is
generated
from
another table as follows: Staff Member: [Completed
Training]![First Name]
&
" " & [Completed Training]!Surname.

From this query I have generated a main report and have included 2
subreports. Everything is working ok except I want my names to
appear as:

Julie Evans, Vicki Gunner, Barry Bartholomew, Peter O'Brien (and
not)

Julie Evans
Vicki Gunner
Barry Bartholomew
Peter O'Brien

I don't know if i suffer from dumb blonde syndrome but I cannot
make heads
nor tales of any of the help suggestions listed. Could someone
please
explain how to do this in easy to follow instructions. Any help
would be
very much appreciated.

 

[Guest]

Thank you both so very much - yes i eventually worked out what you were both
on about and i followed your instructions and my reports are working fine now
- i just wanted to thank you for your help - i couldnt have done it without
you.

MissV,
You can import the module containing the function into your MDB file. This
is done the same as importing tables or other objects File->Get External
Data->Import.
You can then browse to find my sample database and import the module. This
will allow you to use the Concatenate() function like you would use any
other built-in function.

--
Duane Hookom
MS Access MVP

John, Thanks for your help - I'm a temp and have designed this excellent
Training Database using MS Access (proud of how good it really is) - the
only
thing I need to do is to display the names across the page but
unfortunately
I cannot get my head around the Concatenate function, modules, and how
they
all come together(frustrating because I tackled really hard reports and
queries without dramas). I could waste your time all day on this but I
won't
because I've lost total confidence and think I should go and do an
advanced
course - justed wanted to thank you for your help - I have used this
discussion group many times and would be lost without it. - MissV

Did you copy and paste the function Concatenate into a module and then
save the
module with some name other than Concatenate?

Is the function really named "Concatenate" or does it have a different
name?

Your query needs to be rewritten. Any field, query, or table name with
spaces
must be surrounded by brackets []. When you embed a date string you must
surround it with # marks to tell Access that this is a date. The word
Date is a
reserved word in Access and you should if possible rename the Date field
to
something a bit more descriptive like TrainingDate.


Employees: Concatenate("SELECT [Staff member] FROM [qry Proposed Training
by
Date Selection] WHERE [Date] =#" & [Proposed Training by Date
Selection].[Date]
& "#")

Hope this all helps.

MissV wrote:

I have created a query with the following Field, but when I run the
query it
comes up with "Undefined Function 'Concatenate' in expression" - ?

Employee: Concatenate("SELECT Staff member FROM qry Proposed Training
by
Date Selection WHERE Date =" & [Proposed Training by Date
Selection.Date])

:

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP


I have created a query in ms access with the following fields:
date,
course
description, course convenor, staff member. (Staff Member is
generated
from
another table as follows: Staff Member: [Completed
Training]![First Name]
&
" " & [Completed Training]!Surname.

From this query I have generated a main report and have included 2
subreports. Everything is working ok except I want my names to
appear as:

Julie Evans, Vicki Gunner, Barry Bartholomew, Peter O'Brien (and
not)

Julie Evans
Vicki Gunner
Barry Bartholomew
Peter O'Brien

I don't know if i suffer from dumb blonde syndrome but I cannot
make heads
nor tales of any of the help suggestions listed. Could someone
please
explain how to do this in easy to follow instructions. Any help
would be
very much appreciated.