Show field with CRLF ?

[Jason]

In a query, we find that there is a field containing
CRLF. In this way, we can only display the first row of
data in the query result datasheet.

Is it possible for us to change the settings that we are
able to view all content in that field ? We are using
Access 97. We only have to see all information in that
field.

Thanks

 

[Allen Browne]

If the field is named MyMemo, you could make a calculated field in your
query that replaces the CrLf with (say) a space. Type something like this
into the Field row:
Replace(Nz([MyMemo], ""), Chr(13) & Chr(10), " ")

Access 97 and earlier don't have the Replace() function, so you will need to
put this into a standard module:

Function Replace(strExpr As String, strFind As String, strReplace As String,
_
Optional lngStart As Long = 1) As String
Dim strOut As String
Dim lngLenExpr As Long
Dim lngLenFind As Long
Dim lng As Long

lngLenExpr = Len(strExpr)
lngLenFind = Len(strFind)

If (lngLenExpr > 0) And (lngLenFind > 0) And (lngLenExpr >= lngStart)
Then
lng = lngStart
If lng > 1 Then
strOut = Left$(strExpr, lng - 1)
End If
Do While lng <= lngLenExpr
If Mid(strExpr, lng, lngLenFind) = strFind Then
strOut = strOut & strReplace
lng = lng + lngLenFind
Else
strOut = strOut & Mid(strExpr, lng, 1)
lng = lng + 1
End If
Loop
Replace = strOut
End If
End Function

 

[fredg]

In a query, we find that there is a field containing
CRLF. In this way, we can only display the first row of
data in the query result datasheet.

Is it possible for us to change the settings that we are
able to view all content in that field ? We are using
Access 97. We only have to see all information in that
field.

Thanks

You can see all the lines in query datasheet view if you make the row
height taller.

Just place the cursor over the left row selector. Find the row line.
When the cursor turns into a 2-way arrow drag the row line downward as
much as is needed.
Or... Set the row height using Format + Row Height.

You can also scroll within the field using the right arrow key.

If the query is the record source for a report, set the memo field's
control's Can Grow property, as well as the Detail Can Grow, to Yes.

 

[Jason]

Dear Allen,

Thank you for your advice. I would like to know what is
Nz? This is because when I try

Replace([MyMemo], Chr(13) & Chr(10), " ")

It already removes the CrLf.

Thanks

-----Original Message-----
If the field is named MyMemo, you could make a calculated field in your
query that replaces the CrLf with (say) a space. Type something like this
into the Field row:
Replace(Nz([MyMemo], ""), Chr(13) & Chr(10), " ")

Access 97 and earlier don't have the Replace() function, so you will need to
put this into a standard module:

Function Replace(strExpr As String, strFind As String, strReplace As String,
_
Optional lngStart As Long = 1) As String
Dim strOut As String
Dim lngLenExpr As Long
Dim lngLenFind As Long
Dim lng As Long

lngLenExpr = Len(strExpr)
lngLenFind = Len(strFind)

If (lngLenExpr > 0) And (lngLenFind > 0) And (lngLenExpr >= lngStart)
Then
lng = lngStart
If lng > 1 Then
strOut = Left$(strExpr, lng - 1)
End If
Do While lng <= lngLenExpr
If Mid(strExpr, lng, lngLenFind) = strFind Then
strOut = strOut & strReplace
lng = lng + lngLenFind
Else
strOut = strOut & Mid(strExpr, lng, 1)
lng = lng + 1
End If
Loop
Replace = strOut
End If
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

In a query, we find that there is a field containing
CRLF. In this way, we can only display the first row of
data in the query result datasheet.

Is it possible for us to change the settings that we are
able to view all content in that field ? We are using
Access 97. We only have to see all information in that
field.

Thanks

.

 

[Allen Browne]

The Replace() function accepts an argument of type String.
If any of your memo fields are null, the function will barf.
Using Nz() avoids that error.

It would be possible to code the function to operate on a Null, but is was
intentionally written to mimic the behavior of the Replace() function built
into Access 2000 and above.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen,

Thank you for your advice. I would like to know what is
Nz? This is because when I try

Replace([MyMemo], Chr(13) & Chr(10), " ")

It already removes the CrLf.

Thanks

-----Original Message-----
If the field is named MyMemo, you could make a calculated field in your
query that replaces the CrLf with (say) a space. Type something like this
into the Field row:
Replace(Nz([MyMemo], ""), Chr(13) & Chr(10), " ")

Access 97 and earlier don't have the Replace() function, so you will need to
put this into a standard module:

Function Replace(strExpr As String, strFind As String, strReplace As String,
_
Optional lngStart As Long = 1) As String
Dim strOut As String
Dim lngLenExpr As Long
Dim lngLenFind As Long
Dim lng As Long

lngLenExpr = Len(strExpr)
lngLenFind = Len(strFind)

If (lngLenExpr > 0) And (lngLenFind > 0) And (lngLenExpr >= lngStart)
Then
lng = lngStart
If lng > 1 Then
strOut = Left$(strExpr, lng - 1)
End If
Do While lng <= lngLenExpr
If Mid(strExpr, lng, lngLenFind) = strFind Then
strOut = strOut & strReplace
lng = lng + lngLenFind
Else
strOut = strOut & Mid(strExpr, lng, 1)
lng = lng + 1
End If
Loop
Replace = strOut
End If
End Function

In a query, we find that there is a field containing
CRLF. In this way, we can only display the first row of
data in the query result datasheet.

Is it possible for us to change the settings that we are
able to view all content in that field ? We are using
Access 97. We only have to see all information in that
field.

Thanks