Search a memo field

[Sandra]

I'm trying to searh a memo field for carriage return, line feed. When found,
I'd like to insert the number 1 on the first occurance, 2 on the second
occurance, 3 on the the next and so on... Is there a way I can do this?
Thanks in advance.

 

[John Spencer]

Details:
Do you want to replace the carriage return, line feed (crlf)or just insert a
number before or after the crlf? Or do you just want to count the number of
crlf and insert that in a field or in the memo field?

Are you trying to number paragraphs based on this? And if so what do you want
to do if there are two (or more) crlf in a row?

What version of Access?

Is this a permanent change or only temporary?

If a permanent change how do you plan to keep from updating the field content
once it has been changed. I assume you don't want to end up with 1111 instead
of 1.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[fredg]

I'm trying to searh a memo field for carriage return, line feed. When found,
I'd like to insert the number 1 on the first occurance, 2 on the second
occurance, 3 on the the next and so on... Is there a way I can do this?
Thanks in advance.

It's not at all clear what you expect to get from this.
An example would have been helpful, as would your Access version
number.

If your data in the memo field looked like this.
Line A.
Line B.
Line C.
Line D.


Code a command button click event on your form:

Me![MemoField] = AddNumbers([MemoField])

Copy and paste the code below into the form module.


Public Function AddNumbers(strIn As String) As String

Dim intX As Integer
Dim intY As Integer
Dim intZ As Integer
intZ = 1
intX = InStr(strIn, Chr(10))
Do While intX <> 0
strIn = Left(strIn, intX) & intZ & Mid(strIn, intX + 1)
intZ = intZ + 1
intY = intX + 1
intX = InStr(intY, strIn, Chr(10))
Loop
AddNumbers = strIn

End Function

Your data will then look like this:
Line A.
1Line B.
2Line C.
3Line B.

However.....
To display the above datas as:
Line A.1
Line B.2
Line C.3
Line D.4

change the function to:

Public Function AddNumbers(strIn As String) As String

Dim intX As Integer
Dim intY As Integer
Dim intZ As Integer
intZ = 1
intX = InStr(strIn, Chr(10))
Do While intX <> 0
strIn = Left(strIn, intX - 2) & intZ & Mid(strIn, intX - 1)
intZ = intZ + 1
intY = intX + 2
intX = InStr(intY, strIn, Chr(10))
Loop
strIn = strIn & intZ
AddNumbers = strIn

End Function

 

[Sandra]

Thank you for responding and I apologize for the incomplete message.

I'm using 2003. I want to loop through the memo field and insert the
number at the beginning of each of the paragraph I find. Thanks for point
the more than 1 crlf by the way so if there's more than one crlf, delete it
other wise leave it alone.

The desired result is for reporting purposes and does not have to be stored
in the db so if I can do it in the report, it will work to. Each paragraphs
have different lenths so it could just a sentence of multiple sentences.
Here's the formatting look that I'm trying to achieve. No matter how many
times this field is updated, the 1st paragph should always be 1 not 11 or 111.

Memo field content.

Paragraph 1 blah blah blah...
blah blah...

Paragraph 2 blah blah.

I'd like it to look like this.

1 Paragraph 1 blah blah.....
blah blah blah

2 Paragraph 2 blah blah...

Again thank you.

 

[John Spencer]

You can use the following VBA function to do this. Copy the function
and paste it into a VBA module. If it is a new module then save the
module with a name like modParagraphProcedure

In a query call it like this to get single spaced paragraphs
fNumberParagraphs([MemoField],1)

If you want double space between paragraphs replace the 1 with a 2,
triple space between paragraphs then replace the 1 with a 3, etc.



Public Function fNumberParagraphs(strIn, _
Optional iLineSpacing As Integer = 1)
Dim arParagraphs As Variant
Dim strReturn As String
Dim i As Integer, iParaNum As Integer
Dim strLineSpace As String

For i = 1 To iLineSpacing
strLineSpace = strLineSpace & vbCrLf
Next i

If Len(strIn & "") = 0 Then
fNumberParagraphs = strIn

Else
arParagraphs = Split(strIn, vbCrLf)
For i = LBound(arParagraphs) To UBound(arParagraphs)
If Len(arParagraphs(i)) > 1 Then
iParaNum = iParaNum + 1
strReturn = strReturn & _
iParaNum & " " & arParagraphs(i) & strLineSpace
End If
Next i
fNumberParagraphs = _
Left(strReturn, Len(strReturn) - Len(strLineSpace))

End If

End Function

 

[Sandra]

Thank you very much. It is exactly what I'm looking for. This is my first
time in posting and I wasn't quite sure what responses I'll get and I got it.
Once again, thank you.