Memo Field

[Bernie]

Is there a way to force a Return in a memo field? I
import a memo field that is one long string.
Ex.:
R1, R2, R3, R45, R63-R75,R100-R200,R299...

When I use the field in a form or report, I have to fix a
certain width. This means the string could be cutoff at
an important break...i.e. R1 with the 00 (100) forced onto
the next line. I would like to force a return after a
fixed number of commas...or something like that.

Is this possible?
Bernie

 

[Ken Snell [MVP]]

Likely this can be done by a user-defined function that would go through the
memo's string value and insert carriage return and line feed combinations at
various points. Function would need to copy the string one character at a
time and count commas and then insert the characters. Something like this,
perhaps, to get you started:


Public Function InsertCRLFs(strOriginal As String) As String
Dim strNew As String, strT as String
Dim lngPos As Long
Dim intComma As Integer
strNew = ""
intComma = 0
For lngPos = 1 To Len(strOriginal)
strT = Mid(strOriginal, lngPos, 1)
If strT = "," Then intComma = intComma + 1
strNew = strNew & strT
If intComma = 5 Then
strNew = strNew & vbCrLf
intComma = 0
End If
Next lngPos
InsertCRLFs = strNew
End Function

 

[Bernie]

Forgive me ignorance on this. Where would I callout the
code below?

Thanks for the help.
Bernie

 

[Mike Painter]

Is there a way to force a Return in a memo field? I
import a memo field that is one long string.
Ex.:
R1, R2, R3, R45, R63-R75,R100-R200,R299...

When I use the field in a form or report, I have to fix a
certain width. This means the string could be cutoff at
an important break...i.e. R1 with the 00 (100) forced onto
the next line. I would like to force a return after a
fixed number of commas...or something like that.

You could rewrite the string and force line breaks. You'd have to modify the
code if you changed font or width.

"R1, R2, R3, R45, R63-R75,R100-R200,R299..."
if there is a space after the comma, the line will wrap on the space and
will not wrap on a non space.
Rather than rewrite, just run through and make sure the space is there.

 

[Ken Snell [MVP]]

Put the function in a regular module.

Use a query as the report's or form's RecordSource. In that query, replace
the memo field with a calculated field:

CrLfMemo: InsertCRLFs([NameOfMemoField])

 

[Bernie]

Ken,
THANK YOU VERY MUCH. THAT WORKED...BUT I CREATED ONE
ERROR. I GET #ERROR for those memo fields that are blank.
I still want the records to appear (need the other
fields). How can I get it to ignore the blank fields?

AGAIN, THANKS FOR YOUR RESPONSIVENESS AND HELP!!!
Bernie

-----Original Message-----
Put the function in a regular module.

Use a query as the report's or form's RecordSource. In that query, replace
the memo field with a calculated field:

CrLfMemo: InsertCRLFs([NameOfMemoField])

--

Ken Snell
<MS ACCESS MVP>

Forgive me ignorance on this. Where would I callout the
code below?

Thanks for the help.
Bernie fix
a

.

 

[Ken Snell [MVP]]

Try this:

Public Function InsertCRLFs(strOriginal As String) As String
Dim strNew As String, strT as String, strO As String
Dim lngPos As Long
Dim intComma As Integer
strNew = ""
intComma = 0
strO = Nz(strOriginal, "")
For lngPos = 1 To Len(strO)
strT = Mid(strO, lngPos, 1)
If strT = "," Then intComma = intComma + 1
strNew = strNew & strT
If intComma = 5 Then
strNew = strNew & vbCrLf
intComma = 0
End If
Next lngPos
InsertCRLFs = strNew
End Function


--

Ken Snell
<MS ACCESS MVP>

Ken,
THANK YOU VERY MUCH. THAT WORKED...BUT I CREATED ONE
ERROR. I GET #ERROR for those memo fields that are blank.
I still want the records to appear (need the other
fields). How can I get it to ignore the blank fields?

AGAIN, THANKS FOR YOUR RESPONSIVENESS AND HELP!!!
Bernie

-----Original Message-----
Put the function in a regular module.

Use a query as the report's or form's RecordSource. In that query, replace
the memo field with a calculated field:

CrLfMemo: InsertCRLFs([NameOfMemoField])

--

Ken Snell
<MS ACCESS MVP>

Forgive me ignorance on this. Where would I callout the
code below?

Thanks for the help.
Bernie
-----Original Message-----
Likely this can be done by a user-defined function that
would go through the
memo's string value and insert carriage return and line
feed combinations at
various points. Function would need to copy the string
one character at a
time and count commas and then insert the characters.
Something like this,
perhaps, to get you started:


Public Function InsertCRLFs(strOriginal As String) As
String
Dim strNew As String, strT as String
Dim lngPos As Long
Dim intComma As Integer
strNew = ""
intComma = 0
For lngPos = 1 To Len(strOriginal)
strT = Mid(strOriginal, lngPos, 1)
If strT = "," Then intComma = intComma + 1
strNew = strNew & strT
If intComma = 5 Then
strNew = strNew & vbCrLf
intComma = 0
End If
Next lngPos
InsertCRLFs = strNew
End Function

--

Ken Snell
<MS ACCESS MVP>

message
Is there a way to force a Return in a memo field? I
import a memo field that is one long string.
Ex.:
R1, R2, R3, R45, R63-R75,R100-R200,R299...

When I use the field in a form or report, I have to fix
a
certain width. This means the string could be cutoff at
an important break...i.e. R1 with the 00 (100) forced
onto
the next line. I would like to force a return after a
fixed number of commas...or something like that.

Is this possible?
Bernie


.

.

 

[Bernie]

I replaced the code in the module with the code below but
I am still getting the #Error. Any other suggestions?
Thanks again.
Bernie

-----Original Message-----
Try this:

Public Function InsertCRLFs(strOriginal As String) As String
Dim strNew As String, strT as String, strO As String
Dim lngPos As Long
Dim intComma As Integer
strNew = ""
intComma = 0
strO = Nz(strOriginal, "")
For lngPos = 1 To Len(strO)
strT = Mid(strO, lngPos, 1)
If strT = "," Then intComma = intComma + 1
strNew = strNew & strT
If intComma = 5 Then
strNew = strNew & vbCrLf
intComma = 0
End If
Next lngPos
InsertCRLFs = strNew
End Function


--

Ken Snell
<MS ACCESS MVP>

Ken,
THANK YOU VERY MUCH. THAT WORKED...BUT I CREATED ONE
ERROR. I GET #ERROR for those memo fields that are blank.
I still want the records to appear (need the other
fields). How can I get it to ignore the blank fields?

AGAIN, THANKS FOR YOUR RESPONSIVENESS AND HELP!!!
Bernie

-----Original Message-----
Put the function in a regular module.

Use a query as the report's or form's RecordSource. In that query, replace
the memo field with a calculated field:

CrLfMemo: InsertCRLFs([NameOfMemoField])

--

Ken Snell
<MS ACCESS MVP>

Forgive me ignorance on this. Where would I callout the
code below?

Thanks for the help.
Bernie
-----Original Message-----
Likely this can be done by a user-defined function that
would go through the
memo's string value and insert carriage return and line
feed combinations at
various points. Function would need to copy the string
one character at a
time and count commas and then insert the characters.
Something like this,
perhaps, to get you started:


Public Function InsertCRLFs(strOriginal As String) As
String
Dim strNew As String, strT as String
Dim lngPos As Long
Dim intComma As Integer
strNew = ""
intComma = 0
For lngPos = 1 To Len(strOriginal)
strT = Mid(strOriginal, lngPos, 1)
If strT = "," Then intComma = intComma + 1
strNew = strNew & strT
If intComma = 5 Then
strNew = strNew & vbCrLf
intComma = 0
End If
Next lngPos
InsertCRLFs = strNew
End Function

--

Ken Snell
<MS ACCESS MVP>

message
Is there a way to force a Return in a memo field? I
import a memo field that is one long string.
Ex.:
R1, R2, R3, R45, R63-R75,R100-R200,R299...

When I use the field in a form or report, I have

to
fix

a
certain width. This means the string could be cutoff at
an important break...i.e. R1 with the 00 (100) forced
onto
the next line. I would like to force a return

after
a

fixed number of commas...or something like that.

Is this possible?
Bernie


.



.

.

 

[Ken Snell [MVP]]

Be sure that the module's name is different from the function's name.

--

Ken Snell
<MS ACCESS MVP>

I replaced the code in the module with the code below but
I am still getting the #Error. Any other suggestions?
Thanks again.
Bernie

-----Original Message-----
Try this:

Public Function InsertCRLFs(strOriginal As String) As String
Dim strNew As String, strT as String, strO As String
Dim lngPos As Long
Dim intComma As Integer
strNew = ""
intComma = 0
strO = Nz(strOriginal, "")
For lngPos = 1 To Len(strO)
strT = Mid(strO, lngPos, 1)
If strT = "," Then intComma = intComma + 1
strNew = strNew & strT
If intComma = 5 Then
strNew = strNew & vbCrLf
intComma = 0
End If
Next lngPos
InsertCRLFs = strNew
End Function


--

Ken Snell
<MS ACCESS MVP>

Ken,
THANK YOU VERY MUCH. THAT WORKED...BUT I CREATED ONE
ERROR. I GET #ERROR for those memo fields that are blank.
I still want the records to appear (need the other
fields). How can I get it to ignore the blank fields?

AGAIN, THANKS FOR YOUR RESPONSIVENESS AND HELP!!!
Bernie
-----Original Message-----
Put the function in a regular module.

Use a query as the report's or form's RecordSource. In
that query, replace
the memo field with a calculated field:

CrLfMemo: InsertCRLFs([NameOfMemoField])

--

Ken Snell
<MS ACCESS MVP>

message
Forgive me ignorance on this. Where would I callout the
code below?

Thanks for the help.
Bernie
-----Original Message-----
Likely this can be done by a user-defined function that
would go through the
memo's string value and insert carriage return and line
feed combinations at
various points. Function would need to copy the string
one character at a
time and count commas and then insert the characters.
Something like this,
perhaps, to get you started:


Public Function InsertCRLFs(strOriginal As String) As
String
Dim strNew As String, strT as String
Dim lngPos As Long
Dim intComma As Integer
strNew = ""
intComma = 0
For lngPos = 1 To Len(strOriginal)
strT = Mid(strOriginal, lngPos, 1)
If strT = "," Then intComma = intComma + 1
strNew = strNew & strT
If intComma = 5 Then
strNew = strNew & vbCrLf
intComma = 0
End If
Next lngPos
InsertCRLFs = strNew
End Function

--

Ken Snell
<MS ACCESS MVP>

message
Is there a way to force a Return in a memo field? I
import a memo field that is one long string.
Ex.:
R1, R2, R3, R45, R63-R75,R100-R200,R299...

When I use the field in a form or report, I have to
fix
a
certain width. This means the string could be
cutoff at
an important break...i.e. R1 with the 00 (100) forced
onto
the next line. I would like to force a return after
a
fixed number of commas...or something like that.

Is this possible?
Bernie


.



.

.