Access: Adding a new line to a text box via Control Source property?

cadalano
cadalano used Ask the Experts™
on
In MS Access (version shouldn't matter), I have a Text Box control on a form w/ a recordset.

I have set the Control Source on the text box to =Replace([Field], ";", Chr(13))

[Field] is a colon-delimited string, and for each colon I want the string to line break in the text box.

However, Chr(13) is just showing up as a square box in the text field, instead of returning a new line. I know that I can probably get this done easily using VBA, but because the control is in the detail section of a contiguous form, it would be better for me to do it this way if possible. Am I missing something?



I have also tried the following, in place of Chr(13):

Chr(13) & Chr(15)
Chr(15)
"\r"
"\n"
"\r\n"
vbCrLf
vbNewLine

But none of them seem to work as a part of the Control Source property.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Architect / Application Developer
Top Expert 2007
Commented:
Try Chr(13) & Chr(10)

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
=Replace([Field], ";", Chr(13) & Chr(10))

mx
Mike EghtebasDatabase and Application Developer

Commented:
try:

=fnReplace([Field])

With a function call:

Function fnReplace(strField as string) As String

   fnReplace = Replace([Field], ";", vbNewLine)

End Function
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mike EghtebasDatabase and Application Developer

Commented:
Just in case you didn't have any ;

use:

Function fnReplace(strField as string) As String

  If Instr(strField)>0 then
      fnReplace = Replace([Field], ";", vbNewLine)
  Else
      fnReplace = strField
  End If

End Function

Mike
Mike EghtebasDatabase and Application Developer

Commented:
correction... replace [Field] with strField ... sorry

Function fnReplace(strField as string) As String

  If Instr(strField)>0 then
      fnReplace = Replace(strField, ";", vbNewLine)
  Else
      fnReplace = strField
  End If

End Function

Author

Commented:
the solution from DatabaseMX worked perfectly - I was just trying to use Chr(15) instead of Chr(10). Thanks for the help all!!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Thanks Mike ...

mx

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