Memo Field

  • Thread starter Thread starter Bernie
  • Start date Start date
B

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
 
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
 
Forgive me ignorance on this. Where would I callout the
code below?

Thanks for the help.
Bernie
 
Bernie said:
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.
Click to expand...

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.
 
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,
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
Click to expand...


.
Click to expand...
 
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>

Bernie said:
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


.
Click to expand...


.
Click to expand...
Click to expand...
 
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
Click to expand...
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
Click to expand...
after
a
fixed number of commas...or something like that.

Is this possible?
Bernie


.



.
Click to expand...
Click to expand...


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

--

Ken Snell
<MS ACCESS MVP>

Bernie said:
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


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top