text from multiple records in report text box.

  • Thread starter Thread starter Betsy
  • Start date Start date
B

Betsy

I posted this once before and got no replies so maybe this isn't possible.
If it is not please let me know. I'll try to reword this so maybe it makes
more sense.

I have a field in a table called "topic" that is hand entered (no list or
combo box). this is entered by "school" and "month". There may be more than
one entry for a school and month. On the report I would like to list all the
topics for each month/school. So....if school 32 had 4 records with topics in
them...say "safety, homework, just say no, and bus rules for month 2 then I
would like the text box on the summary report to list the topics as such.

topics covered: safety, homework, just say no, bus rules. (comma delimited)

I know how to do this with a listbox in a form, but i can't figure out the
correct code to grab the info from the same field in different records in a
table and have them added to a textbox in a report. I assume that i start
with "DLookup".


Does this make sense? Thanks!
 
I assume that i start with "DLookup".

Actually, you start by redesigning your database. If you need to be able to
view data as you describe, then it should not be in a free form text field as
it is. Parsing through text, even when delimited with commas, is tricky at
best. You can never depend on users to format it like you expect it to be.

To do what you are trying to do, you would have to write some fairly complex
VBA to parse out each individual piece of data, create a record in a
temporary table, an base your report on that table. And I guarantee, you
will find problems where the parsing code didn't work like you hoped it would.

Each of the items you describe should be in a different record in a
redesigned table.
 
I assume you can use the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. Your
control source would be something like:
=Concatenate("SELECT [a field] FROM [TOPIC] WHERE [School]='" & [School] & "
AND [NumericMthField]=" & [NumericMthField])

If this doesn't work, please reply with your actual table and field names as
well as data types and report's record source.
 
thanks, duane,

that actually looks like it might work. I'll give it a try and get back to
you

Duane Hookom said:
I assume you can use the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. Your
control source would be something like:
=Concatenate("SELECT [a field] FROM [TOPIC] WHERE [School]='" & [School] & "
AND [NumericMthField]=" & [NumericMthField])

If this doesn't work, please reply with your actual table and field names as
well as data types and report's record source.
--
Duane Hookom
Microsoft Access MVP


Betsy said:
I posted this once before and got no replies so maybe this isn't possible.
If it is not please let me know. I'll try to reword this so maybe it makes
more sense.

I have a field in a table called "topic" that is hand entered (no list or
combo box). this is entered by "school" and "month". There may be more than
one entry for a school and month. On the report I would like to list all the
topics for each month/school. So....if school 32 had 4 records with topics in
them...say "safety, homework, just say no, and bus rules for month 2 then I
would like the text box on the summary report to list the topics as such.

topics covered: safety, homework, just say no, bus rules. (comma delimited)

I know how to do this with a listbox in a form, but i can't figure out the
correct code to grab the info from the same field in different records in a
table and have them added to a textbox in a report. I assume that i start
with "DLookup".


Does this make sense? Thanks!
Click to expand...
Click to expand...
 
Duane,

I have looked at your sample and your library entry. That is exactly what i
need to do. This is the code I copied and modified from your sample database:

=Concatenate("SELECT[Group Topic] FROM [Group Counseling] WHERE GschoolID ="
& [GSchoolID])

Group Topic is the field
Group Counseling is the table
GSchoolID is the linked field to the Schools table
Month would be the linked field from the Month Table

I have temporarily left out the month parameter to try to simplify things
until i can make it work. then I will add that field also.

I get a parameter error on the word "contatenate". It wants me to put in a
parameter value for "contatenate. I've checkedt he spelling from your
database three times. I'm using Access 2003.

Betsy said:
thanks, duane,

that actually looks like it might work. I'll give it a try and get back to
you

Duane Hookom said:
I assume you can use the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. Your
control source would be something like:
=Concatenate("SELECT [a field] FROM [TOPIC] WHERE [School]='" & [School] & "
AND [NumericMthField]=" & [NumericMthField])

If this doesn't work, please reply with your actual table and field names as
well as data types and report's record source.
--
Duane Hookom
Microsoft Access MVP


Betsy said:
I posted this once before and got no replies so maybe this isn't possible.
If it is not please let me know. I'll try to reword this so maybe it makes
more sense.

I have a field in a table called "topic" that is hand entered (no list or
combo box). this is entered by "school" and "month". There may be more than
one entry for a school and month. On the report I would like to list all the
topics for each month/school. So....if school 32 had 4 records with topics in
them...say "safety, homework, just say no, and bus rules for month 2 then I
would like the text box on the summary report to list the topics as such.

topics covered: safety, homework, just say no, bus rules. (comma delimited)

I know how to do this with a listbox in a form, but i can't figure out the
correct code to grab the info from the same field in different records in a
table and have them added to a textbox in a report. I assume that i start
with "DLookup".


Does this make sense? Thanks!
Click to expand...
Click to expand...
Click to expand...
 
You must have added the Concatenate() function code to a module in your MDB
file.
Your expression
=Concatenate("SELECT[Group Topic] FROM [Group Counseling] WHERE GschoolID ="
& [GSchoolID])
would be entered as the control source of a text box. There should be a
space after SELECT.

Your expression also is written as if GSchoolID is numeric. If it is text,
try:
=Concatenate("SELECT [Group Topic] FROM [Group Counseling] WHERE GschoolID
=""" & [GSchoolID] & """")


--
Duane Hookom
Microsoft Access MVP


Betsy said:
Duane,

I have looked at your sample and your library entry. That is exactly what i
need to do. This is the code I copied and modified from your sample database:

=Concatenate("SELECT[Group Topic] FROM [Group Counseling] WHERE GschoolID ="
& [GSchoolID])

Group Topic is the field
Group Counseling is the table
GSchoolID is the linked field to the Schools table
Month would be the linked field from the Month Table

I have temporarily left out the month parameter to try to simplify things
until i can make it work. then I will add that field also.

I get a parameter error on the word "contatenate". It wants me to put in a
parameter value for "contatenate. I've checkedt he spelling from your
database three times. I'm using Access 2003.

Betsy said:
thanks, duane,

that actually looks like it might work. I'll give it a try and get back to
you

Duane Hookom said:
I assume you can use the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. Your
control source would be something like:
=Concatenate("SELECT [a field] FROM [TOPIC] WHERE [School]='" & [School] & "
AND [NumericMthField]=" & [NumericMthField])

If this doesn't work, please reply with your actual table and field names as
well as data types and report's record source.
--
Duane Hookom
Microsoft Access MVP


:

I posted this once before and got no replies so maybe this isn't possible.
If it is not please let me know. I'll try to reword this so maybe it makes
more sense.

I have a field in a table called "topic" that is hand entered (no list or
combo box). this is entered by "school" and "month". There may be more than
one entry for a school and month. On the report I would like to list all the
topics for each month/school. So....if school 32 had 4 records with topics in
them...say "safety, homework, just say no, and bus rules for month 2 then I
would like the text box on the summary report to list the topics as such.

topics covered: safety, homework, just say no, bus rules. (comma delimited)

I know how to do this with a listbox in a form, but i can't figure out the
correct code to grab the info from the same field in different records in a
table and have them added to a textbox in a report. I assume that i start
with "DLookup".


Does this make sense? Thanks!
Click to expand...
Click to expand...
Click to expand...
 
I did add it to the text box control source. GSchoolID is numeric.
Thanks for your help. Any other suggestions?

Duane Hookom said:
You must have added the Concatenate() function code to a module in your MDB
file.
Your expression
=Concatenate("SELECT[Group Topic] FROM [Group Counseling] WHERE GschoolID ="
& [GSchoolID])
would be entered as the control source of a text box. There should be a
space after SELECT.

Your expression also is written as if GSchoolID is numeric. If it is text,
try:
=Concatenate("SELECT [Group Topic] FROM [Group Counseling] WHERE GschoolID
=""" & [GSchoolID] & """")


--
Duane Hookom
Microsoft Access MVP


Betsy said:
Duane,

I have looked at your sample and your library entry. That is exactly what i
need to do. This is the code I copied and modified from your sample database:

=Concatenate("SELECT[Group Topic] FROM [Group Counseling] WHERE GschoolID ="
& [GSchoolID])

Group Topic is the field
Group Counseling is the table
GSchoolID is the linked field to the Schools table
Month would be the linked field from the Month Table

I have temporarily left out the month parameter to try to simplify things
until i can make it work. then I will add that field also.

I get a parameter error on the word "contatenate". It wants me to put in a
parameter value for "contatenate. I've checkedt he spelling from your
database three times. I'm using Access 2003.

Betsy said:
thanks, duane,

that actually looks like it might work. I'll give it a try and get back to
you

:

I assume you can use the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. Your
control source would be something like:
=Concatenate("SELECT [a field] FROM [TOPIC] WHERE [School]='" & [School] & "
AND [NumericMthField]=" & [NumericMthField])

If this doesn't work, please reply with your actual table and field names as
well as data types and report's record source.
--
Duane Hookom
Microsoft Access MVP


:

I posted this once before and got no replies so maybe this isn't possible.
If it is not please let me know. I'll try to reword this so maybe it makes
more sense.

I have a field in a table called "topic" that is hand entered (no list or
combo box). this is entered by "school" and "month". There may be more than
one entry for a school and month. On the report I would like to list all the
topics for each month/school. So....if school 32 had 4 records with topics in
them...say "safety, homework, just say no, and bus rules for month 2 then I
would like the text box on the summary report to list the topics as such.

topics covered: safety, homework, just say no, bus rules. (comma delimited)

I know how to do this with a listbox in a form, but i can't figure out the
correct code to grab the info from the same field in different records in a
table and have them added to a textbox in a report. I assume that i start
with "DLookup".


Does this make sense? Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Please reply to: "You must have added the Concatenate() function code to a
module in your MDB file"

--
Duane Hookom
Microsoft Access MVP


Betsy said:
I did add it to the text box control source. GSchoolID is numeric.
Thanks for your help. Any other suggestions?

Duane Hookom said:
You must have added the Concatenate() function code to a module in your MDB
file.
Your expression
=Concatenate("SELECT[Group Topic] FROM [Group Counseling] WHERE GschoolID ="
& [GSchoolID])
would be entered as the control source of a text box. There should be a
space after SELECT.

Your expression also is written as if GSchoolID is numeric. If it is text,
try:
=Concatenate("SELECT [Group Topic] FROM [Group Counseling] WHERE GschoolID
=""" & [GSchoolID] & """")


--
Duane Hookom
Microsoft Access MVP


Betsy said:
Duane,

I have looked at your sample and your library entry. That is exactly what i
need to do. This is the code I copied and modified from your sample database:

=Concatenate("SELECT[Group Topic] FROM [Group Counseling] WHERE GschoolID ="
& [GSchoolID])

Group Topic is the field
Group Counseling is the table
GSchoolID is the linked field to the Schools table
Month would be the linked field from the Month Table

I have temporarily left out the month parameter to try to simplify things
until i can make it work. then I will add that field also.

I get a parameter error on the word "contatenate". It wants me to put in a
parameter value for "contatenate. I've checkedt he spelling from your
database three times. I'm using Access 2003.

:

thanks, duane,

that actually looks like it might work. I'll give it a try and get back to
you

:

I assume you can use the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. Your
control source would be something like:
=Concatenate("SELECT [a field] FROM [TOPIC] WHERE [School]='" & [School] & "
AND [NumericMthField]=" & [NumericMthField])

If this doesn't work, please reply with your actual table and field names as
well as data types and report's record source.
--
Duane Hookom
Microsoft Access MVP


:

I posted this once before and got no replies so maybe this isn't possible.
If it is not please let me know. I'll try to reword this so maybe it makes
more sense.

I have a field in a table called "topic" that is hand entered (no list or
combo box). this is entered by "school" and "month". There may be more than
one entry for a school and month. On the report I would like to list all the
topics for each month/school. So....if school 32 had 4 records with topics in
them...say "safety, homework, just say no, and bus rules for month 2 then I
would like the text box on the summary report to list the topics as such.

topics covered: safety, homework, just say no, bus rules. (comma delimited)

I know how to do this with a listbox in a form, but i can't figure out the
correct code to grab the info from the same field in different records in a
table and have them added to a textbox in a report. I assume that i start
with "DLookup".


Does this make sense? Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Oops! I think I missed that. I'll get back to you.

Thanks!

Duane Hookom said:
Please reply to: "You must have added the Concatenate() function code to a
module in your MDB file"

--
Duane Hookom
Microsoft Access MVP


Betsy said:
I did add it to the text box control source. GSchoolID is numeric.
Thanks for your help. Any other suggestions?

Duane Hookom said:
You must have added the Concatenate() function code to a module in your MDB
file.
Your expression
=Concatenate("SELECT[Group Topic] FROM [Group Counseling] WHERE GschoolID ="
& [GSchoolID])
would be entered as the control source of a text box. There should be a
space after SELECT.

Your expression also is written as if GSchoolID is numeric. If it is text,
try:
=Concatenate("SELECT [Group Topic] FROM [Group Counseling] WHERE GschoolID
=""" & [GSchoolID] & """")


--
Duane Hookom
Microsoft Access MVP


:

Duane,

I have looked at your sample and your library entry. That is exactly what i
need to do. This is the code I copied and modified from your sample database:

=Concatenate("SELECT[Group Topic] FROM [Group Counseling] WHERE GschoolID ="
& [GSchoolID])

Group Topic is the field
Group Counseling is the table
GSchoolID is the linked field to the Schools table
Month would be the linked field from the Month Table

I have temporarily left out the month parameter to try to simplify things
until i can make it work. then I will add that field also.

I get a parameter error on the word "contatenate". It wants me to put in a
parameter value for "contatenate. I've checkedt he spelling from your
database three times. I'm using Access 2003.

:

thanks, duane,

that actually looks like it might work. I'll give it a try and get back to
you

:

I assume you can use the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. Your
control source would be something like:
=Concatenate("SELECT [a field] FROM [TOPIC] WHERE [School]='" & [School] & "
AND [NumericMthField]=" & [NumericMthField])

If this doesn't work, please reply with your actual table and field names as
well as data types and report's record source.
--
Duane Hookom
Microsoft Access MVP


:

I posted this once before and got no replies so maybe this isn't possible.
If it is not please let me know. I'll try to reword this so maybe it makes
more sense.

I have a field in a table called "topic" that is hand entered (no list or
combo box). this is entered by "school" and "month". There may be more than
one entry for a school and month. On the report I would like to list all the
topics for each month/school. So....if school 32 had 4 records with topics in
them...say "safety, homework, just say no, and bus rules for month 2 then I
would like the text box on the summary report to list the topics as such.

topics covered: safety, homework, just say no, bus rules. (comma delimited)

I know how to do this with a listbox in a form, but i can't figure out the
correct code to grab the info from the same field in different records in a
table and have them added to a textbox in a report. I assume that i start
with "DLookup".


Does this make sense? Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Duane,

this worked beautifully! thanks so much!

betsy said:
Oops! I think I missed that. I'll get back to you.

Thanks!

Duane Hookom said:
Please reply to: "You must have added the Concatenate() function code to a
module in your MDB file"

--
Duane Hookom
Microsoft Access MVP


Betsy said:
I did add it to the text box control source. GSchoolID is numeric.
Thanks for your help. Any other suggestions?

:

You must have added the Concatenate() function code to a module in your MDB
file.
Your expression
=Concatenate("SELECT[Group Topic] FROM [Group Counseling] WHERE GschoolID ="
& [GSchoolID])
would be entered as the control source of a text box. There should be a
space after SELECT.

Your expression also is written as if GSchoolID is numeric. If it is text,
try:
=Concatenate("SELECT [Group Topic] FROM [Group Counseling] WHERE GschoolID
=""" & [GSchoolID] & """")


--
Duane Hookom
Microsoft Access MVP


:

Duane,

I have looked at your sample and your library entry. That is exactly what i
need to do. This is the code I copied and modified from your sample database:

=Concatenate("SELECT[Group Topic] FROM [Group Counseling] WHERE GschoolID ="
& [GSchoolID])

Group Topic is the field
Group Counseling is the table
GSchoolID is the linked field to the Schools table
Month would be the linked field from the Month Table

I have temporarily left out the month parameter to try to simplify things
until i can make it work. then I will add that field also.

I get a parameter error on the word "contatenate". It wants me to put in a
parameter value for "contatenate. I've checkedt he spelling from your
database three times. I'm using Access 2003.

:

thanks, duane,

that actually looks like it might work. I'll give it a try and get back to
you

:

I assume you can use the generic concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. Your
control source would be something like:
=Concatenate("SELECT [a field] FROM [TOPIC] WHERE [School]='" & [School] & "
AND [NumericMthField]=" & [NumericMthField])

If this doesn't work, please reply with your actual table and field names as
well as data types and report's record source.
--
Duane Hookom
Microsoft Access MVP


:

I posted this once before and got no replies so maybe this isn't possible.
If it is not please let me know. I'll try to reword this so maybe it makes
more sense.

I have a field in a table called "topic" that is hand entered (no list or
combo box). this is entered by "school" and "month". There may be more than
one entry for a school and month. On the report I would like to list all the
topics for each month/school. So....if school 32 had 4 records with topics in
them...say "safety, homework, just say no, and bus rules for month 2 then I
would like the text box on the summary report to list the topics as such.

topics covered: safety, homework, just say no, bus rules. (comma delimited)

I know how to do this with a listbox in a form, but i can't figure out the
correct code to grab the info from the same field in different records in a
table and have them added to a textbox in a report. I assume that i start
with "DLookup".


Does this make sense? Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top