Solved

Access Report Expression that adds a new line between strings

Posted on 2008-06-19
18
960 Views
Last Modified: 2013-11-28
I am making an Access Report that pulls data from an Access query.  Nothing too difficult.  However, the one field is a string that has multiple lines.  For a line break, a square character is used to show to start a new line.  While this works in most applications, access simply reads it in as a character and displays the square instead of making a new line in the textbox that the field is bound to.

Could anyone give me an example of an expression that would simulate hitting return when it finds a certain character in an Access field?
0
Comment
Question by:zrancidr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 4
18 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21822582
you can use  vbcrlf  to break the line

dim msg

msg="the quick brown fox " & vbcrlf & "jumps over"

msgbox msg

post sample data from your table
0
 

Author Comment

by:zrancidr
ID: 21822636
Here is an example, however, the text editor on experts exchange accepts the square symbol as a line break...so I'll use [] in place of it.

Example:
"WATCH CUBE                    [] 2-1/4"" X 2-1/4"" X 2""H           []WITH WATCH SUPPORT"
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 21822687
Thinking maybe character is ascii cr or lf. So not full crlf.

Just a thought, if you had this and use the vaue returned from here to display

Public Function ShowCRLF(ByVal sString) As String

    Dim i As Integer
   
    If sString = "" Then
        ShowCRLF = ""
        Exit Function
    End If
       
    For i = 1 To Len(sString)
        If Mid$(sString, i, 1) = Chr$(13) Or Mid$(sString, i, 1) = Chr$(10) Then
            ShowCRLF = ShowCRLF & vbCrLf
        Else
            ShowCRLF = ShowCRLF & Mid$(sString, i, 1)
        End If
    Next i
End Function

0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 65

Expert Comment

by:rockiroads
ID: 21822697
Hmm, what am I doing, chr$(13) is same as vbcrlf
So perhaps its just the line feed character you got then chr$(10)

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 21822715
There is also the option of checking for all non readable characters by just checking ascii range. It then depends on what to you want displayed.
0
 

Author Comment

by:zrancidr
ID: 21822717
Sorry, a little lost in your code.

Could you place in jobDescription (it's the column name) where it would be needed.

Just having some problem following it :)
0
 

Author Comment

by:zrancidr
ID: 21822738
Simply put, if it reads the character [], it should starting the next part of the string on the next line.

Well, maybe it's not so simple :-P
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21822973
try this

update TableX set Fieldx=Replace(fieldx, chr(13), chr(13) & chr(10))

or

update TableX set Fieldx=Replace(fieldx, chr(10), chr(13) & chr(10))

if you attach your db here, we can check what the square symbol is chr(9),chr(10) or chr(13)
0
 

Author Comment

by:zrancidr
ID: 21823183
Sounds like a plan.  Here is a sample database that includes one instance of the square character.  Sorry, I'm not too fluent in VB, so thanks for taking the time.
Database1.mdb
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 21823273
Ok, your query is based on some sql?

You could put this function in vba code somewhere, then change your sql

select field1, field2, ShowCRLF(jobDescription) as JDescription, field3
from ...

This way it leaves your original data intact

Your report can then use JDescription as the field


But not sure if that is the case though

if you did

select jobDescription from mytable

in a query, what do you get. Do you get square brackets?
If so, if you did

select ShowCRLF(jobDescription) from mytable

what do you get now?

It could be other characters and not return characters

If so then use this to remove it

coming up...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21823311
it is chr(10) , use this update query


UPDATE test SET test.PartDescription = Replace([PartDescription],Chr(10),Chr(13) & Chr(10));
0
 

Author Comment

by:zrancidr
ID: 21823343
will this change the actual database though?  I can't change the database.
0
 

Author Comment

by:zrancidr
ID: 21823384
SELECT ShowCRLF(PartNum)
FROM table;

This didn't work.  Didn't know what ShowCRLF was.

Just to clarify, I am running a query to create this report.  I cannot change the format of the database at all, I just need to change how it displays on the report.

Thanks!
0
 

Author Comment

by:zrancidr
ID: 21823431
UPDATE test SET test.PartDescription = Replace([PartDescription],Chr(10),Chr(13) & Chr(10));

This did work, but of course it changes the database.

I might try to have it insert the field into a new table maybe and format it from there...but then I guess that query would have to run everytime the report is ran.

Does this sound like it should work?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 300 total points
ID: 21823461
then just run a select query

SELECT Replace([PartDescription],Chr(10),Chr(13) & Chr(10)) AS PartDesc
FROM test;
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 200 total points
ID: 21823474
Ok, using your sample

SELECT ShowCRLF(test.PartDescription)
FROM test;


It has removed the square brackets, you need to expand the row to see the text

(this is in the query)
0
 

Author Closing Comment

by:zrancidr
ID: 31468800
Thanks for all the hard work guys! Going to go with caps, seems to work great!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 21823904
No worries

by the way, I just saw your comment

This didn't work.  Didn't know what ShowCRLF was.

When you paste the method in a vba module, you gotta save that module before you can use it
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question