Query with formatting

Dear Experts

Courtesy of EE I have a query that joins to fields together, the query will ultimately get exported to RTF

Name Title: [Candidate] &  Chr(13) & Chr(10) & [Job Title]

This works great, but what I would like to be able is have the [Candidate] in bold when it exports to RTF if that's possible?
John Doe
Head of Customer Relations

Can anybody help?
Who is Participating?
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
If you just ran the query, can you manually copy/paste and get the formatting in Word?
Gustav BrockCIOCommented:
Yes. Have a default RTF string where you replace two tokens with your field values:

Public Function BuildRTF(ByVal strCandidate As String, ByVal strJobTitle As String) As String

  Dim strRTF As String
  Dim strExport As String

  strRTF = "{\rtf1\ansi\ansicpg1252\b %1 \b0\par %2}"
  strExport = Replace(Replace(strRTF, "%1", strCandidate), "%2", strJobTitle)

  BuildRTF = strExport
End Function

Then, in your query:

  Name Title: BuildRTF(Nz([Candidate]), Nz([Job Title]))

correlateAuthor Commented:
Hi Gustav

Thanks for this, the coding goes in all fine, unfortunately when I run the export routine the whole code comes out i.e. I get

{\rtf1\ansi\ansicpg1252\b XXXXXX \b0\par Marketing Director}

rather than

Marketing Director
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Gustav BrockCIOCommented:
Of course. You wrote it was for export.
It must be read by a viewer like Word or WordPad that understands RTF encoded text.

Jeffrey CoachmanMIS LiasonCommented:
1. Why not just use a report for this?
Then this is easy...

2. If you are using Access 2007(or higher) and the new .accdb format, you can use a the new "Rich Text" formatting option to do this as well, but here again, this is quite a bit of work when you can do this in a report in less than a minute...

Use Access 2007 or higher
Use the .accdb format
Create a query like this:
    SELECT "<b>" & [Thename] & "</b><br>" & [TheTitle] AS NameTitle
Then set the "TEXT FORMAT" property (NOT the "Format" property) of this field to: Rich Text

Sample of both attached

correlateAuthor Commented:
Hi Jeff

Thanks for this - will give it a go in the morning, I did try something like this before posting the question, but for some reason I couldn't get it to export to word with the formatting in place - will have a look through your sample & give it a go

Many thanks
Jeffrey CoachmanMIS LiasonCommented:
This displays the text as you want "In Access".

You can try exporting both to rtf and see what happens.

Can I ask why this is needed in Word?
Perhaps the same thing can be accomplished in Access.
correlateAuthor Commented:
Hi Jeff,
Thanks for your comments - I tried the export from both the report & query, but alas neither came out formatted correctly.

Also the reason for not exporting from a report was the fact that I need the results to be in table format rate than tab delimitated (sorry I should have mentioned this in the initial question).

The reason for this being produced in word / RTF is a long story, but yes ideally these reports should be produced inside access and exported to PDF for emailing - that would that make my life so much easier, but alas that's not doable in this instance.
correlateAuthor Commented:
I've been trying that out and, whilst it's clunky it works - will post the end result & allocate points tomorrow as I'm on my mobile at present.  Many thanks for your help
Jeffrey CoachmanMIS LiasonCommented:
I only want points if a manual Copy/paste is your only option...

Just a little background info.

"Rich text" in Access 2007 and newer is really HTML, ...so I only offered the query to see if it would work.
I was not sure if true "Rich Text" (.rtf file format) was what was required...

The copy/paste from the query probably worked because the query outputs the actual HTML formatted values.  The newer formats of Word, can take in this value directly

< ideally these reports should be produced inside access and exported to PDF for emailing - that would that make my life so much easier, but alas that's not doable in this instance. >
...ok, because this is simple to do in Access 2007 or higher, sorry that this is not an option for you...


correlateAuthor Commented:
Here's the work around...

1. The text I wanted to be in bold (the name) I placed a wrapper on the name in the query so the result came out at ||John Doe>> followed by his job title.

2. Ran the export and opened the RTF doc

3. Recorded a word macro which did a find & replace for everything between << & >> and replaced it with the same text but in bold.

4. I then copied that piece of the code and placed it into the access code by applying it to the 'active document' which was then coded to close saving changes.

A bit messy but it works - the full code is below
Dim WordApp As Word.Application
Dim WordDocument As Word.Document
Set WordApp = CreateObject("Word.Application")
WordApp.Documents.Open (OutputFileShortlist)
WordApp.Visible = True
'manipulate RTF doc bit
    With ActiveDocument
        If Selection.PageSetup.Orientation = wdOrientPortrait Then
        Selection.PageSetup.Orientation = wdOrientLandscape
        Selection.PageSetup.Orientation = wdOrientPortrait
    End If
    Selection.Find.Replacement.Font.Bold = True
    With Selection.Find
        .Text = "(||)(*)(\>\>)"
        .Replacement.Text = "\2"
        .Forward = True
        .Wrap = wdFindAsk
        .Format = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchAllWordForms = False
        .MatchSoundsLike = False
        .MatchWildcards = True

    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    .Close (Word.WdSaveOptions.wdSaveChanges)
    Set WordApp = Nothing
    End With

Open in new window

correlateAuthor Commented:
Thanks for all your help - the code is posted in my last comment
Jeffrey CoachmanMIS LiasonCommented:
Great work there...!

Then actually it would be a bigger help to others if you split the points between yourself and me.

My post was the suggestion, but your post above was the actual "solution".
Therefore your post should be marked as the solution, and mine should be the "assisted"

Again, great work there, ...soon you wont need us at all..

correlateAuthor Commented:
Hi Jeff,

I tried to split them but at the time it just wasn't letting me do so, which I thought was because I would have to allocate the minimum 20 points to me.
Jeffrey CoachmanMIS LiasonCommented:
Then to be honest, I would actually prefer if you just accepted only your post.

You showed a lot of skill here in doing this on your own.
This is a unique issue, and your workaround is probably as good as this will get. (with the current technology)


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.