Maintine Access formatting in query results

I have an Access database with records containing a Memo field. The text in the field has formatting applied to it such as carriage returns and line breaks. Theses are not designated by characters like cr or lf. It is simply formatted as in this text box. When I output this field to the screen from a query result I lose all carriage return and line break formatting. How do I keep this formatting???
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
Chr(13) is a carriage return character
Chr(10) is a line feed character
together they indicate a new line

Use this function to indicate these characters are actually in your memo field(s)

Also, the purpose of a query datasheet is to display the dynaset returned by the query.  The purpose of a form or report is to format that data for output to either screen or other medium.  You should use one of these to view your data correctly formatted.

OM Gang
Public Function FindNewLine()
On Error GoTo Err_FindNewLine
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strTableName As String, strMemoFieldName As String
    Dim varResult As Variant
    strTableName = "MyTable"
    strMemoFieldName = "MemoFieldName"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strTableName)
    Do Until rs.EOF
        varResult = InStr(rs(strMemoFieldName), Chr(13) & Chr(10))
        Debug.Print varResult
    Set rs = Nothing
    Set db = Nothing
    Exit Function
    MsgBox Err.Number & ", " & Err.Description, , "Error"
    Resume Exit_FindNewLine
End Function

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
omgangIT ManagerCommented:
I guess I should have added the function will send it's results to the Immediate window so make sure to have it open.  Make sure to replace the values for strTableName and strMemoFieldName with those from your database.

OM Gang
spiderbuzzAuthor Commented:
Forgive me omgang,
So I would call this from within <cfoutput> tags and # 's?
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

spiderbuzzAuthor Commented:
Actually I solved it using a ParagraphFormat() function on the output.

omgangIT ManagerCommented:
<So I would call this from within <cfoutput> tags and # 's?>

No, you'd use it in the Access db to check for those special characters.

Glad you found a work-around.
OM Gang
spiderbuzzAuthor Commented:
I should have made it clear that I was looking for a ColdFusion solution. That which I am using is not a work around but a ColdFusion solution. I see no reason to run an additional procedure in the database when CF will maintain the formatting already present. Thanks for your answer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.