Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2010
    Posts
    6

    Unanswered: Need Memo data type to remain Memo

    Hello - I am fairly new to the access and VB world and i am running into a problem. I have created a database that pulls the raw data from a SharePoint site. There are a couple of sections within the SharePoint site that contain project descriptions and comments. Many of these entries are over 255 characters. The problem that i am having is when i run a query that contains a module to remove characters such as (<div>) from the entry. When this query is run, it turns these fields into text data types and in the final report, these fields are incomplete due to the 255 character limit. Can anyone tell me what code in the module is causing this conversion of the memo data type to a text data type? The code for the module is below:

    Option Compare Database

    Public Function PrepareForMetaTag(ByVal sString As String) As Variant

    Dim result, ResultL, ResultR, sDelimiter, sDelimiter2 As String
    Dim iCompare As Long
    Dim iPosition, iPosition2 As Integer
    iCompare = vbBinaryCompare
    result = ""
    sDelimiter = "<"
    sDelimiter2 = ">"

    result = Trim(sString) & " erasethis777"
    result = Replace(result, vbCrLf, " ")
    If LenB(result) > 5000 Then
    result = "String is too long, data not processed"
    MsgBox ("String is too long, data not processed")
    End If
    ResultR = ""
    ResultL = ""

    iPosition = InStr(1, result, sDelimiter, iCompare)
    iPosition2 = InStr(1, result, sDelimiter2, iCompare)

    Do While iPosition > 0
    ResultL = Left$(result, iPosition - 1)
    ResultR = Right$(result, Len(result) - iPosition2)
    result = ResultL & " " & ResultR
    iPosition = InStr(1, result, sDelimiter, iCompare)
    iPosition2 = InStr(1, result, sDelimiter2, iCompare)
    Loop

    result = Replace(result, ". .", ".") ' removes duplicate punctuation
    result = Replace(result, "erasethis777", "")
    result = Replace(result, " ", " ") ' removes double spacing
    result = Replace(result, "&nbsp;", " ") ' removes double spacing

    PrepareForMetaTag = Trim(result)

    End Function

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its a fairly well known flaw in JET
    I suspect its becuase the datatype returned from replace is a string column
    what you may have to do is write your own function to do the replacement
    try defining the variable result as memo

    incidentally
    Code:
    Dim result, ResultL, ResultR, sDelimiter, sDelimiter2 As String
    isn't doing what you think it is
    its defining sDelimiter2 As String, and the rest as type variant
    Last edited by healdem; 12-08-10 at 13:24.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •