Memo Field adjust display

[Tonesterman]

My company has a DB that has a memo field that is VERY long. Is there any way to adjust the display format of this field. As of right now I am trying the following:
Mid([LONG_MEMO_FIELD],1,65), Mid([LONG_MEMO_FIELD],66,65), Mid([LONG_MEMO_FIELD],131,65), Mid([LONG_MEMO_FIELD],196,65). But this way puts each line in a column, and I would prefer it all in one field.

My goal is to display all the lines of data in a letter. The max characters in a like is 65, but the amount of lines is not fixed. Hopefully this makes since.

Any help would be great!

 

[SoCalAccessPro]

Concatentate using the ampersend (&)

= Mid([LONG_MEMO_FIELD],1,65) & Mid([LONG_MEMO_FIELD],66,65), Mid([LONG_MEMO_FIELD],131,65)& Mid([LONG_MEMO_FIELD],196,65)

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[fneily]

By "letter" do you mean Word or an Access Report? If a report, don't know why you have to split up a memo field.

 

[MajP]

not sure what you are actually doing but you could use a function like this

Public Function breakMemo(strMemo As Variant, intLength As Integer) As String
  Dim intBegin As Integer
  intBegin = 1
  Do
    If intBegin = 1 Then
     breakMemo = Mid(strMemo, intBegin, intLength)
    Else
      breakMemo = breakMemo & vbCrLf & Mid(strMemo, intBegin, intLength)
    End If
    intBegin = intBegin + intLength
  Loop Until Len(Mid(strMemo, intBegin, intLength)) = 0
End Function

so if
x = "abcdefg"
then
y = breakMemo(x,3)
returns

abc
def
g

 

[Tonesterman]

Sorry if this was confusing. Here is an example of what is in the memo field:
***************************************
NAME ADDRESSLINE 1 ADDRESS LINE 2 ADDRESS LINE 3 ADDRESS LINE 4 ADDRESS LINE 5 DEAR SIR, THE FOLLOWING IS A TEST REGARDS TESTER
***************************************
what i am trying to accomplish is to export this information into a word document letter.

I would like the result to read as follows:
***************************************
NAME
ADDRESSLINE 1
ADDRESS LINE 2
ADDRESS LINE 3
ADDRESS LINE 4
ADDRESS LINE 5

DEAR SIR,

THE FOLLOWING IS A TEST

REGARDS
TESTER
***************************************

This is a very small example, some of the letters would be well over 200 lines, so doing the mid function would be a nightmare.

 

[Tonesterman]

Thanks MajP that codes seems to have worked