Very long Memo field

[elioch]

Hi there,

I need to truncated the content of a Memo field, but the Left,Right
Mid functions come up with an error message that a comma (,) cannot b
used.

Also I would like to select which line of the text in the Memo field i
later displayed in a report.

Can anybody help?

elioc

 

[John Spencer (MVP)]

Not enough information.

You can truncate a field using LEFT([SomeField],500) or

Left([Somefield],Instr(1,[SomeField],",")-1)

or other variations.

WHAT SPECIFICALLY have you tried? Post it, so it can be examined and corrected
if needed.

 

[elioch]

Thanks John for your reply.

I am using Access with linked tables from another database and when
try to use the Left function to shorten the contents of the fiel
'notes' on the table 'position' it returns the following error when
try to run the query - Syntax error (comma) in query expressio
'position. [Left([notes],6)]'

I know I have used this before, but for some reason now it does no
work.

Sorry for the bother.

Elioc

 

[John Spencer (MVP)]

If Position is the table name and notes is the field name then

Left([Position].[notes],6)

Otherwise

Left([Notes],6)

 

[elioch]

Hi John,

Option 1 worked well.Thanks.

Is there a way to identify and pick up different lines of a Memo field
We are talking here of an external database and I want to distinguis
between was goes into the first, second and third line, for example
There is a CR at the end of each line.

Thanks

elioc

 

[John Spencer (MVP)]

This might best be handled with a custom function.

The First Line is easy. Just look for the CRLF combo - Chr(13) & Chr(10) - and
chop off the length there.

Left([FieldName], Instr(1,[FieldName] & Chr(13) & Chr(10),Chr(13) & Chr(10))-1)

The custom vba function would be a bit more complex to write. What version of
Access are you using? If 2000 or later then you can use the split function to
make the writing of the function quite a bit easier

 

[elioch]

I am using Office 97

 

[Douglas J. Steele]

Check http://support.microsoft.com/?id=188007 for one approach to
implementing the Split function in Access.

Note, though, that in Access 97, you'll have to change the line

VbCompareMethod = vbBinaryCompare

to

Long = vbBinaryCompare

since Access 97 doesn't support Enums.

 

[John Spencer (MVP)]

In that case (Access 97), you need code something like the following UNTESTED
CODE in a module.

Public Function getPart(strIn as String, intPartNum as integer)
'Call this with getPart([YourFieldName],1) to get the first section.
Dim vArray as variant

vArray = Split(StrIn, vbCrLf)

If UBound(vArray)>intPartNum Then
getPart = vArray(intPartNum-1)
Else
getPart = ""
End IF

End Function

'I got the following from a post in the users groups and I believe it was
'posted by MVP John Viescas. If not, I owe him an apology for posting his
'code to the internet and perpetuating the breaking of his copyright.

Public Function Split(strToSplit As String, _
Optional strDelimiter As String = " ", _
Optional intCount As Integer = -1, _
Optional intCompare As Integer = 0) As Variant
'-----------------------------------------------------------
' Inputs: String to search,
' delimiter string,
' optional replacement limit (default = -1 .. ALL)
' optional string compare value (default vbBinaryCompare)
' Outputs: Array containing items found in the string
' based on the delimiter provided
' Created By: JLV 09/05/01 John Viescas
' Last Revised: JLV 09/05/01
' ** Duplicates the functionality of the VB 6 SPLIT function.
'-----------------------------------------------------------
Dim strWork As String, intCnt As Integer, intIndex As Integer
Dim intI As Integer, strArray() As String

If (intCompare < 0) Or (intCompare > 2) Then
Err.Raise 5
Exit Function
End If
strWork = strToSplit
intCnt = intCount
' If count is zero, return the empty array
If intCnt = 0 Then
Split = strArray
Exit Function
End If
' If the Delimiter is zero-length, return a 1-entry array
If strDelimiter = "" Then
ReDim strArray(0)
strArray(0) = strWork
Split = strArray
Exit Function
End If

' Decrement count by 1 because function returns
' whatever is left at the end
intCnt = intCnt - 1
' Loop until the counter is zero
Do Until intCnt = 0
intI = InStr(1, strWork, strDelimiter, intCompare)
' If delimiter not found, end the loop
If intI = 0 Then Exit Do
' Add 1 to the number returned
intIndex = intIndex + 1
' Expand the array
ReDim Preserve strArray(0 To intIndex - 1)
' Use index - 1 .. zero-based array
strArray(intIndex - 1) = Left(strWork, intI - 1)
' Remove the found entry
strWork = Mid(strWork, intI + 1)
intCnt = intCnt - 1
Loop
' Put anything left over in the last entry of the array
If Len(strWork) > 0 Then
intIndex = intIndex + 1
ReDim Preserve strArray(0 To intIndex - 1)
strArray(intIndex - 1) = strWork
End If

' Return the result
Split = strArray

End Function