bullet points in Access 2010 Report

Question

I'm working on changing an Access 2010 report. There is a portion of the report that reads in Raw Data from a SharePoint field called "Notes" that has comments for each record. Within the "notes" field, there can be several sentences. I need to find a way to separate those sentences into a bullet point per sentence in my report.

I'm trying to come up with a clever way to do so. I can get the data entry folks to use a symbol of some sort in the raw that signifies the need for a new bullet. This way, in my Report Expression (or perhaps via VBA), I can get it separated... but how?

Any thoughts?

Fionnuala · Answer 1 · 2014-01-27T16:40:28.870

2

The memo data field in MS Access can be set to Rich Text as a property, so:

 UPDATE Table1 
 SET Table1.AMemo = "<ul><li>" & 
     IIf(InStr([AMemo],".")>0,
     Replace(Mid([AMemo],1,Len([AMemo])-1),".","</li>"),[AMemo]) & "</li></ul>"

RichText

edited Jan 27 '14 at 16:40

answered Jan 27 '14 at 16:13

Fionnuala

90,370
7
114
152

Ah yes, Memo fields can do that now. Very modern. :) +1 – Gord Thompson Jan 27 '14 at 16:26
Interesting. So, if I understand it correctly, any time a "." exists, then it returns a new line and a bullet? – AnalyzeThis Jan 28 '14 at 03:55
You need to update a memo field with the query to get the bullet points, unless you wish to write full HTML and output to disk, rather than use reports. – Fionnuala Jan 28 '14 at 08:50
This is adding bullet points where needed, but also where they are not needed. It's adding 5 extra points after all the memo text... and where there is no text in the memo field, it is still adding 5 bullet points. Strange. – AnalyzeThis Jan 28 '14 at 14:07
Only full stops are being replaced in the code. What is you exact text? – Fionnuala Jan 28 '14 at 15:53
See comment below Gord's response regarding my solution. – AnalyzeThis Jan 28 '14 at 21:59

score 1 · Accepted Answer · answered Jan 27 '14 at 16:18

In its most rudimentary form you could do something like the following. Is splits the [Notes] text on ". " and creates a separate "point" for each sentence.

Sample Data: [SharePointData]

SlideNumber  Notes                               
-----------  ------------------------------------
          1  Title slide.                        
          2  Brief overview. Just the highlights.
          3  More stuff.

VBA Code:

Option Compare Database
Option Explicit

Public Function SplitNoteText(RawText As Variant) As Variant
    Dim rtn As Variant, StringArray() As String, Point As Variant
    Const BulletChar = "-"
    rtn = Null
    If Not IsNull(RawText) Then
        rtn = ""
        StringArray = Split(RawText, ". ", -1, vbBinaryCompare)
        For Each Point In StringArray
            If Len(Point) > 0 Then
                If Len(rtn) > 0 Then
                    rtn = rtn & vbCrLf & vbCrLf
                End If
                rtn = rtn & BulletChar & " " & Point
                If Right(Point, 1) <> "." Then
                    ' add back the period that got "consumed" in the Split
                    rtn = rtn & "."
                End If
            End If
        Next
    End If
    SplitNoteText = rtn
End Function

Test query:

SELECT SlideNumber, Notes, SplitNoteText(Notes) AS Points FROM SharePointData;

Results:

SlideNumber  Notes                                 Points                                     
-----------  ------------------------------------  ----------------------
          1  Title slide.                          - Title slide.                             
          2  Brief overview. Just the highlights.  - Brief overview.

                                                   - Just the highlights.
          3  More stuff.                           - More stuff.

This code makes a little more sense to me than Remou's. I suppose both work. I have to figure out which one is more appropriate for my db. — AnalyzeThis, Jan 28 '14 at 03:59
I made a slight adjustment to this. The data entry employees will be using "*" to denote a new bullet point in the Comments field of the SharePoint records. For some reason, when transfering the query to a report, Access wasn't adding the space. I had to end up using "" at the end of one of the statements. Thanks for the help :p — AnalyzeThis, Jan 28 '14 at 21:57

bullet points in Access 2010 Report

2 Answers2