SQL 2008 add notes field

HKFuey used Ask the Experts™
I'm looking for some advice on the data format to use for notes added to a record.

My code adds a date stamp and text and these are displayed in a text box on an Access form.

It looks like this:
23/05/12 Note3 24/05/12 Note2 22/05/12 Note1

How do I make it look like this: -
23/05/12 Note3
24/05/12 Note2
22/05/12 Note1

I tried using & VBCr but this does not seem to work.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try using Chr(10) or Chr(13) instead of VBCr. Also try using a combination: Chr(10) & Chr(13) and the other way around as well.

Also make sure your text box allows showing multiline text. Otherwise it will just cut the new lines, so what you have in the database will be ignored and changed to spaces.


OK, when I print the results (immediate window) I get new lines for each comment.

But the text box just concatenates them all!
In design mode of the form, select the text box and review its properties. I am not an Access expert but google-ing "multiline text box access" provides some good results. Have a look at them and see if they would work for you.

One I saw was: Select the textbox then goto Properties - Other and set Enter Key Behavior to New Line.
Hamed NasrRetired IT Professional

try this click event of a button:
Assume field name v = "23/05/12 Note3 24/05/12 Note2 22/05/12 Note1"

Private Sub Command5_Click()
    Dim ar() As String  ' array to hold substrings
    Dim i, j As Integer
    ar = Split(v, " ")
    i = UBound(ar())
    For j = 0 To i - 1 Step 2
        Debug.Print ar(j), ar(j + 1)
    Next j
End Sub


I used a combination  of Chr(10) or Chr(13)  and Enter Key Behavior to New Line. It now works! (I did have Chr(13) or Chr(10)  the other way round as well)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial