• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4004
  • Last Modified:

MS Access - VBA - Chr(13) not working

I'm trying to put a hard return using Chr(13) into 3 different places
a Listbox.Caption,
a text field in a table
and a memo field in a table

Using VBA in MS Access 2007

In the listbox I get a square box with a ? mark in it
In the text and memo fields I get a space (ascii 32)

Other Chr functions are working, like Chr(34)

A simple sample is like this
sText = sText + Chr(13) + sLicense

I can manually put the return in the text\/memo fields by by pressing Crtl+Return so I know the field accepts this character

While this is probably stupid/easy I cant find any reason its not working, everything says it should work, and points are high because I am in a hurry




0
allan1956
Asked:
allan1956
2 Solutions
 
Wayne Taylor (webtubbs)Commented:
Try using vbCrLf instead....

    sText = sText & vbCrLf & sLicense

And use the ampersand (&) for string concatenations.

Wayne
0
 
rockiroadsCommented:
you might need to include a chr$(10) with the chr$(13) but what webtubbs should do the same and save you from using chr


0
 
allan1956Author Commented:
Hi, thanks
Well partial success.  Using & didn't change anything

Using vbvbCrLf  did make the label.Caption work, the return/line breaks are there

However,still no luck on the database text/memo fields.  Which was more important

If I cut/paste the text from the memo filed into a hex editor, I see that Ascii 32 is whats there - a space

This happens when using Chr(10) as well


0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
TextReportCommented:
Hi allan1956, in VBA if you are using + then if any of your variables are NULL then your result will be NULL, normally you would use & rather than + as

Null + "A" = Null
Null & "A" = "A"

Chr(13) & Chr(10) is the same as vbCrLf and I use this all over the place including populating text and memo fields. It is not possible to use vbCrLf in a query and you have to use Chr(13) & Chr(10) and if has to be CR LF and not LF CR.

Below is a test from a question yesterday that shows it works in Access 2007 on the query, this is then value in one field combining several fields.

Cheers, Andrew
ISSUES: 1
STAFFING: 2
SCHEDULE: 3
SCOPE: 4
FINANCIALS: 5
RISKS: 6

Open in new window

0
 
TextReportCommented:
Found it, can you check the Text Format of the Memo Field, it will be set to Rich Text. For Rich Text fields you need to use "</P>" instead of Chr(13) & Chr(10).
Cheers, Andrew
0
 
allan1956Author Commented:
ok, that was it, using html in the memo field- i used a <br /> when witting to the and it worked.  I  cut/paste the date into a hex editor and found that writing out <br /> actually stores as 0D0A or Chr(13)Chr(10)

Strange and not all that intuitive.  

So vbCrLf for the label caption and <br /> for the memo field
0
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now