Inserting a carriage return

[jldunn2]

I am an extreme novice but I need to figure out how to rearrange some
data in an Access field. I have 3 to 4 strings of text separated by 2
or more spaces. I want to strip out the spaces and insert in their
place a carriage return. I've found plenty of code to do the opposite,
but I can't figure out how to insert the carriage return.

Thanks!

Jer

 

[benJamin]

Try the Access constants vbCr or vbCrLf to add a Carriage Return or a
Carriage Return and a Line Feed.

Just concatenate them to the string:

aString = Left(aString,InStr(aString," ")-1) & vbCrLf &
Right(aString,InStr(aString," "))

Or something similar.

benJamin

 

[jldunn2]

Thanks for your reply. I have very little experience using Access so
forgive my cluelessness. Do I incorporate the above into a module and
then use a query to perform this action, or is there another method of
using the above code?

Thanks in advance.

Jer

 

[benJamin]

Definitely no the most elegent solution. There are almost certainly
other functions provided by VBA that could improve efficiency.
However, I hope this also presents a bit of a tutorial on some of the
more important concepts of VBA programming, should you not already no
them (hence the ludicrously labourious comments, which you can omit as
required).

Hope this helps.



Public Function ReplaceSpacesWithCRLF(text As String) As String
'
-AssumeCompleteInexperienceMode ON
'
;-)
' Explicitly define variables to ensure future spelling errors are
easy to trace.
Dim leftText As String
Dim rightText As String
Dim pos As Integer
Dim leftPos As Integer
Dim endSpace As Integer

' Initialize variables to ensure they start as expected
leftText = ""
rightText = ""

' Then, check each character in the input string (text)
pos = 1
leftPos = 1
endSpace = 0
While pos <= Len(text)
pos = InStr(pos, text, " ")
' If there are any spaces at all, then perform procedure
If pos > 0 Then
' See if there are multiple spaces in a row
If Mid(text, pos, 1) = " " Then
' If so, take part up to spaces and add a carriage return and line
feed
leftText = leftText + Mid(text, leftPos, pos - leftPos)
leftPos = pos
' Then process remainder of stream to strip subsequent spaces
rightText = Mid(text, pos)
endSpace = 1
Do
' We know the first character is a space, so check the next
endSpace = endSpace + 1
' If it is also a space, repeat the loop until otherwise
Loop Until Mid(rightText, endSpace, 1) <> " "
' Set checking to continue from the next non-space character
pos = pos + endSpace
leftPos = leftPos + endSpace - 1
' If there were two or more spaces, insert a carriage return
If endSpace > 2 Then
If endSpace + pos < Len(text) Then
leftText = leftText & vbCrLf
End If
' Otherwise, there was only one space, so we make sure it's added
Else
leftText = leftText & " "
End If
End If
' If there are no spaces left (or at all), add the remaining text and
exit the loop
Else
pos = Len(text) + 1
leftText = leftText & Mid(text, leftPos)
End If
Wend

ReplaceSpacesWithCRLF = leftText
End Function

 

[benJamin]

Excuse poor proof-reading; should say 'not', not 'no' and, further
down, 'know', not 'no'.

me=Dictionary.stupid

;-)

benJamin