SELECT [Compliance Topics].ID, [Compliance Topics].[Topic #], [Compliance
Topics].[Columbia Compliance Topic Description], [Compliance Topics].Status,
[Compliance Topics].International, [Compliance Topics].[Columbia Theme],
[Compliance Topics].[2006 Assessment Grouping], [Compliance Topics].[Old
RCSA
Description (2006 and earlier)], [Compliance Topics].[RCSA Template],
[Compliance Topics].[MOMS Theme], [Compliance Topics].[MOMS Topic
Description], Replace([Compliance Topics].[ICA 1940], ",", Chr(13) &
Chr(10)), [Compliance Topics].[IAA 1940],
[Compliance Topics].NASD, [Compliance Topics].[Other Regulation],
[Compliance
Topics].[2007 CAT Review Schedule], [Compliance Topics].[Fictitious CAT
Dates
(for design testing)], [Compliance Topics].[2007 Risk Ranking HML],
[Compliance Topics].[Date Added], [Compliance Topics].[Date Removed],
[Compliance Topics].[CRM Contact], [Compliance Topics].[2006 Inherent Input
Level of External Emphasis and Scrutiny], [Compliance Topics].[2007 Inherent
Input Financial Impact], [Compliance Topics].[2007 Inherent Input Overall
Applicability to the LOB], [Compliance Topics].[2007 Environmental Input
Procedural Change Rank], [Compliance Topics].[2007 Environmental Input
Personnel Change Rank], [Compliance Topics].[2007 Environmental Input System
Change Rank], [Compliance Topics].[2007 Environmental Input Other Related
Changes Rank], [Compliance Topics].[2007 Total Risk Rating Score],
[Compliance Topics].[CRM 2nd Contact]
FROM [Compliance Topics]
WHERE ((([Compliance Topics].Status)="Active")
Note that this will change the name of the field [ICA 1940]: you'll have to
make sure you change your report to pick up the new field name.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
E_Chase said:
SELECT [Compliance Topics].ID, [Compliance Topics].[Topic #], [Compliance
Topics].[Columbia Compliance Topic Description], [Compliance
Topics].Status,
[Compliance Topics].International, [Compliance Topics].[Columbia Theme],
[Compliance Topics].[2006 Assessment Grouping], [Compliance Topics].[Old
RCSA
Description (2006 and earlier)], [Compliance Topics].[RCSA Template],
[Compliance Topics].[MOMS Theme], [Compliance Topics].[MOMS Topic
Description], [Compliance Topics].[ICA 1940], [Compliance Topics].[IAA
1940],
[Compliance Topics].NASD, [Compliance Topics].[Other Regulation],
[Compliance
Topics].[2007 CAT Review Schedule], [Compliance Topics].[Fictitious CAT
Dates
(for design testing)], [Compliance Topics].[2007 Risk Ranking HML],
[Compliance Topics].[Date Added], [Compliance Topics].[Date Removed],
[Compliance Topics].[CRM Contact], [Compliance Topics].[2006 Inherent
Input
Level of External Emphasis and Scrutiny], [Compliance Topics].[2007
Inherent
Input Financial Impact], [Compliance Topics].[2007 Inherent Input Overall
Applicability to the LOB], [Compliance Topics].[2007 Environmental Input
Procedural Change Rank], [Compliance Topics].[2007 Environmental Input
Personnel Change Rank], [Compliance Topics].[2007 Environmental Input
System
Change Rank], [Compliance Topics].[2007 Environmental Input Other Related
Changes Rank], [Compliance Topics].[2007 Total Risk Rating Score],
[Compliance Topics].[CRM 2nd Contact]
FROM [Compliance Topics]
WHERE ((([Compliance Topics].Status)="Active") AND (([Compliance
Topics].[ICA 1940])=Replace([ICA 1940],",",Chr(13) & Chr(10))));
--
Thank you!
E_Chase
KARL DEWEY said:
Post your SQL statement. Open the query in design view, click on menu
VIEW
- SQL View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little
:
My initial response to your suggestion appears to have been wrong - the
problem is that the recommended query is only pulling line items from
my
datase with a single entry in the field at issue- it is ignoring the
line
items with multiple entries (the ones with the comma separators). So
the
issue is not that the secondard+ lines aren't showing on the report, it
is
that they are not being extracted by the new query in the first place.
--
I really appreciate your continued help.
E_Chase
:
A couple of things --
1 - What is the alias name of the expression you built? Expr1?
2 - Did you change the Control Source to the alias name?
3 - Run the query alone and expand the datasheet view vertically by
moving
your cursor over the horizontal line seperating the rows and dragging
the
grid down.
--
KARL DEWEY
Build a little - Test a little
:
Karl -
Thank you for the quick reply!
I tried editing my query in the manner you described. It seems to
have
gotten my half way toward my goal - the report recognizes the
commas, but it
is now cutting off all of the information beyond the first comma
(either that
or the additional rows are just not appearing for some reason).
If, for
example, I had four items strung together, the report now just
shows the
first item. I have the properties field set to "can grow = yes" so
that is
not the problem. Any other thoughts?
--
Thanks again!
E_Chase
:
In the query feeding the report use this on the field ---
Replace([YourTextField], ",", Chr(13) & Chr(10))
This puts a return and line feed in place of the comma.
--
KARL DEWEY
Build a little - Test a little
:
I have a field in my database that contains numerous records,
separated by a
comma (for example: apple, banana, orange, pear). When I add
the field to a
report in Access, it is displayed as a single line of
horizontal data (as in
the example). I want the data to appear in a vertical format
(shown below):
apple
banana
orange
pear
Any thoughts on how to format the field to change the
presentation of the
data? I would be willing to change the source data from comma
separators to
something else if that would provide the desired end result. I
am a new
Access user so I would appreciate any input or suggestions.
