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?
correlateAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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]))

/gustav
0
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

XXXXXX
Marketing Director
0
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.

/gustav
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

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

JeffCoachman
Database155.accdb
0
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
0
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.
0
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.
0
Jeffrey CoachmanMIS LiasonCommented:
If you just ran the query, can you manually copy/paste and get the formatting in Word?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
0
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...

:-(

JeffCoachman
0
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
    Else
        Selection.PageSetup.Orientation = wdOrientPortrait
    End If
    
    Selection.WholeStory
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    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

0
correlateAuthor Commented:
Thanks for all your help - the code is posted in my last comment
0
Jeffrey CoachmanMIS LiasonCommented:
Hey,
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..
;-)

JeffCoachman
0
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.
0
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)

;-)

JeffCoachman
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.