Is there a way of programmatically putting a CR into a cell in a table

[flo999]

Within a cell in a table in Access I am trying to replace a certian character (say %) with a CR (carriage return)- to have more than one line in the cell

Any ideas how this can be done?

 

[tlbroadbent]

Use Chr(13) & Chr(10).

Example:
Insert table1 (colA) Values("1st line" & Chr(13) & Chr(10) & "2nd line&quot Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[Dav43]

Hi Flo999, I don't understand quite what you want to do? If you want to make the fields in your table bigger, so you can get more than one line, why don't you resize them by dragging the left hand margin down?

David

LOL

 

[mymou]

Could wirte a public Function() and then use the function in a update query (or wherever).

Stew

 

[flo999]

Thanks, I have done that - here is my function,which I am calling from an updte query, but it does not recognise the chr(10) nor chr(13) - just replaces the character (in my example A) with a square.

Function FindA(WhichField As String) As String
Dim x As Integer, strText As String
Dim start As Integer
start = 1
x = 1
strText = WhichField

Do Until x = 0
' Chr(65) is the Tab character.
' Replace Chr(65) with the ANSI code for the character you are searching for.
x = InStr(start, strText, Chr(65))
start = x + 1
If x > 0 And Not IsNull(x) Then
strText = ReplaceA(x, strText)
End If
Loop

FindA = strText
End Function


Function ReplaceA(start As Integer, strText As String) As String
' Replace % with the character you want to substitute.
Mid(strText, start, 1) = Chr(13)
ReplaceA = strText
End Function

 

[mymou]

not sure if i have missed something - but

Function ReplaceA(strText ,strReplace)

strText = Excel.WorksheetFunction.Substitute(strText, strReplace, vbCrLf)
'excel function used here - think there is something avaiable in access 2000 (replace)
ReplaceA = strText


End Function

Just trying to keep it simple if possible.

 

[tlbroadbent]

There are dozens (hundreds?) of ways to solve problems - from simple to complex, easy to hard, etc. Whether you write a function or use SQL code, you need to use the ASCII characters for carriage return (chr(13)) and linefeed (chr(10)) in that order to create a linefeed in the Access cell. Otherwise, the characters appear as "little squares."

I apologize I didn't read the original carefully enough. You can use Replace in Access 2000.

Update tbl Set col=replace(col,"str",chr(13) & chr(10))
Where col like "*str*"

If you don't have Access 2000 you can write a function as has been suggested and use that function in an update query or you can use the following update query.

Update tbl Set col = Left$([col],InStr([col],"str&quot-1) & Chr(13) & Chr(10) & Right([col],Len([col])-InStr([col]," &quot+1)
Where col like "*str*" Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot