Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Posts
    3

    Unanswered: Retaining formatting in memo fields (reports)

    I am being passed an MDB file from a backend system that uses SQL server to store information. The conversion of SQL-server to MDB seems to work well except when I encounter a memo or text field containing CR/LF characters.

    Many of the fields in the database are Memo fields and hold 'verbal' responses that will comprise of up to 2000 characters.

    The problem that i am having is that the memo fields seem to be substituting carriage return/linefeed for a strange square character and when I use the memo field in a report I lose all formatting of the input data.

    The result is something along the lines of

    Original
    This is a list of data items

    Item one
    Item two
    Item three

    When I include the memo field in the report I get something along the lines of

    This is a list of data items[][]Item one[]Item two[]Item three[]

    The [] indicate the square I actually see in the text.

    How can I convert the square character back to a CR/LF ?

    Any help would be greatly appreciated.

    Thanks in advance

    Andy

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Because the visible TextBox in a Report is normally bound to a specific piece of incoming data, I'm going to ask you to try something different. First, make a backup of the Report in question.

    Pick one of your Report TextBox or Memo fields and make it Unbound (copy the ControlSource property first). For the sake of a name, let's say this field is named myFirstMemoField.

    Now, create a new TextBox within the same section that contains the myFirstMemoField field (this is usually within the Details Section). Name this new TextBox myFirstHiddenMemoField and bind this to the original ControlSource that was contained within myFirstMemoField.

    Now open a code window to the OnFormat event for the Details Section of your Report and enter code something like this:

    Code:
    Dim Strg As String
    Dim FinalStrg As String
    Dim i As Long
    If Not IsNull(Me.myFirstHiddenMemoField) Then
       For i = 1 to Len(Me.myFirstHiddenMemoField)
    	  Strg=Mid$(Me.myFirstHiddenMemoField, i, 1)
    	  If Strg = Chr$(13) Then FinalStrg = FinalStrg & vbNewLine: Goto ShipIt
    	  If Strg = Chr$(10) Then Goto ShipIt
    	  FinalStrg = FinalStrg & Strg
    SkipIt:
       Next i
       If Strg <> "" And (Strg <> Chr$(13) Or Strg <> Chr$(10)) Then 
    	  FinalStrg = FinalStrg & Strg
       End If
       Me.myFirstMemoField = FinalStrg
    End If
    ** The above code is Not Tested **

    Save your report then run you DB and display your report. Do you still see the little squares?


  3. #3
    Join Date
    Nov 2004
    Posts
    3

    Lost the squares - but still no CR/LF

    CyberLynx

    Thanks for the suggestion, I copied the code as suggested and it seems to have got rid of the square boxes. Unfortunately it does not appear to have converted them to cr/lf or line breaks.

    Any other thoughts on this ?

    Thanks again

    Andy

  4. #4
    Join Date
    Nov 2004
    Posts
    3

    Ah .. Gottit

    CyberLynx

    I changed your code slightly - copied the action for Chr$(13) to Chr$(10) making the two lines;

    If Strg = Chr$(13) Then FinalStrg = FinalStrg & vbNewLine: Goto ShipIt
    If Strg = Chr$(10) Then FinalStrg = FinalStrg & vbNewLine: Goto ShipIt

    Not really sure what this is now doing but it works With this change I lose the squares and get back my carriage returns.

    Thanks very much for your help

    Andy

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    You might want to try:

    From this:

    If Strg = Chr$(13) Then FinalStrg = FinalStrg & vbNewLine: Goto ShipIt

    To this:

    If Strg = Chr$(13) Or Strg = Chr$(13) & Chr$(10) Then FinalStrg = FinalStrg & vbNewLine: Goto ShipIt

    Or to this:

    If Strg = Chr$(13) Or Strg = Chr$(10) Then FinalStrg = FinalStrg & vbNewLine: Goto ShipIt



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •