Query with formatting

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

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

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

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

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

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.

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.

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

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

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

Select allOpen in new window

Thanks for all your help - the code is posted in my last comment

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

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.

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