Solved

Concatenating Access rich text memos into a single field

Posted on 2010-08-23
12
790 Views
Last Modified: 2013-11-28
Using Access 2010 I have a rich text field on a form, constructed by concatenating the contents of 20 rich text memo fields in a table (fields named Detail1..Detail20). If the user wants to edit one or more of the fields (all some or none of which can be left blank), they double-click on the field on the form to pop-up a sub-form that allows them to edit each field individually.

The concatenation is currently done in the query which is the data source for the form, very simply by: [Detail1] & [Detal2] & ... & [Detail20], although I can replace this with a VBA function once I'm sure I can get the thing to work as I want.

The size of each individual Detail field is likely to be relatively short (but not fixed) and it's important that different fields or parts of fields can be shown in Bold or Italic. The formatting will be defined by the user and be different for different records. The concatenated result will appear in reports as well as on the form, and is the way the users will almost always view the data.

The problem I have is that the concatenation process puts a hard return between the individual fields, instead of presenting them as one continuous paragraph. Is this inherent in the Rich Text format (i.e. I can't get around it); or will a function let me achieve what I'm looking for?

If the answer is a VBA function, a coding example would be great.
0
Comment
Question by:colevalleygirl
  • 7
  • 4
12 Comments
 
LVL 84
ID: 33499429
How are you concatenating the values into one field? You won't get a hard return unless you enter one (either via code or the keyboard).
0
 

Author Comment

by:colevalleygirl
ID: 33499493
The query contains a calculated field:

[Detail1] & [Detail2] & [Detail3] all the way up to [Detail20]

Each Detail field in testing has a single line of text, no return at the end, e.g.

Detail1: ab,
Detail2: cd

Concatenated result:

ab,
cd

when what I want is:

ab,cd

(If the user chose to put in a return, that would be fine, but if they didn't I don't want one to magically appear).
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33503526
It is hard to visualize this without a screenshot...

But it looks like there IS a line break in there somewhere...
...and how is Detail1 "Magically" getting the comma (,) after it?

Are you dong something else to the query besides the concatenation?
Can you post the entire SQL please?

JeffCoachman
0
 

Author Comment

by:colevalleygirl
ID: 33503707
Detail1 has the comma included in the memo.

SQL is:

SELECT tblSource.*, tblTemplate.SourceType, [SourceDetail1] & [SourceDetail2] & [SourceDetail3] & [SourceDetail4] & [SourceDetail5] & [SourceDetail6] & [SourceDetail7] & [SourceDetail8] & [SourceDetail9] & [SourceDetail10] & [SourceDetail11] & [SourceDetail12] & [SourceDetail13] & [SourceDetail14] & [SourceDetail15] & [SourceDetail16] & [SourceDetail17] & [SourceDetail18] & [SourceDetail19] & [SourceDetail20] AS Citation, *
FROM tblTemplate RIGHT JOIN tblSource ON tblTemplate.TemplateID = tblSource.TemplateID
ORDER BY tblSource.SourceName;

Image of the citation field and editing form is attached.

Clipboard01.jpg
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 33504238
I can't see anything that would "Force" a wrap, like you are getting.
...unless this is a byproduct of using Formatted Memo fields...

Is the Rich (HTML, actually) text formatting absolutely needed?
(In other words, do you really need to format individual words?)

Because if plain text works, you can format the control that displays the entire concatenated string (txtCitation?) in one style...

;-)

JeffCoachman
0
 

Author Comment

by:colevalleygirl
ID: 33504521
Yes, I really need to format individual words/phrases or even individuals elements of a phrase, to give something like:

James T Author, A Riveting Book

I suspect it must be a byproduct of using formatted memo fields -- something to do with the HTML that is stored.

I guess my options at this point are to look at 3rd party Rich Text Controls, or start tinkering with the HTML strings. Ugh.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:colevalleygirl
ID: 33504641
This works:

    If Not IsNull(varPhrase) Then
        varPhrase= Left(varPhrase, Len(varPhrase) - 6) 'Remove the trailing </div>
        varPhrase= Right(varResult, Len(varResult) - 5) 'remove the leading <div>
    End If
0
 

Author Comment

by:colevalleygirl
ID: 33504651
Should be:

    If Not IsNull(varPhrase) Then
        varPhrase= Left(varPhrase, Len(varPhrase) - 6) 'Remove the trailing </div>
        varPhrase= Right(varPhrase, Len(varPhrase) - 5) 'remove the leading <div>
    End If
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33504930
OK
0
 

Author Comment

by:colevalleygirl
ID: 33505057
I meant to allocate points to boag2000 but somehow failed to do so -- can I object to my own closure please?
0
 

Author Closing Comment

by:colevalleygirl
ID: 33505065
boag2000 pointed me in the right direction to solve my own problem,
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33505353
;-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now