What's the character code for the return key?

  • Thread starter Thread starter Sirocco
  • Start date Start date
Thanks. Since I have your attention, I'm working with a memo field, and I'm
trying to isolate strings based on the end of line character - I'm trying to
isolate lines of text. Is this possible? So far I'm experimenting with
the Mid and Instr functions. Do you have any experience with this?

Thanks.
 
I don't know what you mean by "isolate lines of text". Many of us have lots
of experience with Mid and Instr.
 
It's possible that more than one line-ending character may be in use within
the same string, because you get a different character when you press
Ctrl+Enter than when you press Enter alone. Other line ending characters may
potentially be present if data has been imported from other sources. The
technique I use in Access 2000 and later is to first replace all these
characters with null characters, then replace any sequences of multiple null
characters with single null characters, and finally use the Split function
with the null character as the delimiter ...

Public Function SplitLines(ByVal strInput As String) As Variant

Dim lngCounter As Long
Dim strOutput As String
Dim strChar As String

strOutput = strInput
For lngCounter = 10 To 13
strChar = Chr$(lngCounter)
strOutput = Replace(strOutput, strChar, vbNullChar, 1, -1,
vbBinaryCompare)
Next lngCounter

Do While InStr(1, strOutput, vbNullChar & vbNullChar) > 0
strOutput = Replace(strOutput, vbNullChar & vbNullChar, vbNullChar,
1, -1, vbBinaryCompare)
Loop

SplitLines = Split(strOutput, vbNullChar, -1, vbBinaryCompare)

End Function

Public Sub TestSplitLines()

Dim strTest As String
Dim varTest As Variant
Dim varLoop As Variant

strTest = "This is the first line" & Chr$(10) & _
"and this is the second line" & Chr$(11) & _
"and this is the third line" & Chr$(12) & _
"and this is the fourth line" & Chr$(13) & _
"and this is the fifth line" & Chr$(13) & Chr$(10) & _
"and this is the sixth and final line"
varTest = SplitLines(strTest)
For Each varLoop In varTest
Debug.Print varLoop
Next varLoop

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Thanks! But what does the output look like after running this program?
Will this program work under the following circumstance: I have a memo
field with 1 or several lines of text, with most lines ending with the
return key (each line actually has similar structure - eventually I'll break
each line into separate fields). But right now I just want to break teach
whole line in this memo field into it's own text field, so I'd basically be
adding a record after each line ends, and putting the next line in that new
record, which would repeat for as many lines are in the memo field, then the
memo field of the next record would be read, and so on. Microsoft should
have a built-in routine to do this.
Thanks again for any help.
 
The second procedure that I posted, TestSplitLines, exists for the sole
purpose of showing you what the output looks like. Just copy and paste the
two procedures, then run the second one and see for yourself.

The first procedure returns a variant array, in which each element in the
array contains one line of text. The second procedure shows how you can take
this variant array and iterate over it, in this example printing each
element (each line) to the Immediate window. All you need to do now is
modify that procedure to add each element (line) to a recordset instead of
printing it to the Immediate window.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Back
Top