Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    16

    Unanswered: search and replace on memo fields

    I have a table that I have imported from dBase III which includes a memo field. All of the data imports correctly EXCEPT the memo field contains the little black box that indicates a chr(13) (I think). I would like to do a search and replace on that little box, replacing it with a space so that Access can do its own paragraph formatting.

    Is there a way to do this with the task bar Replace selection and/or is there a way to do this with VB code? I am using Access 2000.

    Thank you.

  2. #2
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    enjoy
    jiri

    Sub ReplaceMemo()
    Dim myRecSet As New ADODB.Recordset
    Dim myConnection As String

    'you Can use CurrentProject.connection for current opened MDB or any other connection string
    'Connection = CurrentProject.Connection
    myConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=c:\winnt\desktop\db1.mdb;" & _
    "User Id=admin;" & _
    "Password="

    myRecSet.Open " SELECT * FROM myTable", myConnection, adOpenDynamic, adLockOptimistic

    Do While Not myRecSet.EOF
    'here you can use your own special function (I use my own)
    'or MS ACCESS2000 REPLACE function
    myRecSet.fields("MemoField").Value = REPLACE(myRecSet.fields("MemoField").Value, chr(13),"")
    ' I didn't test MS Access REPLACE fce cauze I have here Access97 only.
    myRecSet.Update
    myRecSet.MoveNext
    Loop

    myRecSet.Close
    End Sub

  3. #3
    Join Date
    Jan 2003
    Posts
    16
    [QUOTE][SIZE=1]Originally posted by playernovis
    enjoy
    jiri

    Thanks for such a quick reply. I will give this a try. I can also have the 97 version of Access, so I could try whatever you have. I will post back as soon as I have a chance to try this.
    Thanks again

  4. #4
    Join Date
    Jan 2003
    Posts
    16

    Thumbs up

    It works as advertised! I just had to register Microsoft ActiveX 2.5 library. Thanks.

  5. #5
    Join Date
    Mar 2004
    Posts
    56

    I need help with this script please

    I absolutely could use this formula but need a bit of help entering it. I am using access 2k and would like to use the replace function but I am not one who really understands macros all that well and need help knowing where to enter in/replace wording within this script.

    heres my info

    Table: combined_PS
    field: VNDAD1
    need to find "%"
    need to replace with "Null"

    I have already guessed that I need to change the data source path to where my .mdb is located but I am a bit lost on the rest. I would appreciate any assistance offered.





    Sub ReplaceMemo()
    Dim myRecSet As New ADODB.Recordset
    Dim myConnection As String

    'you Can use CurrentProject.connection for current opened MDB or any other connection string
    'Connection = CurrentProject.Connection
    myConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=c:\winnt\desktop\db1.mdb;" & _
    "User Id=admin;" & _
    "Password="

    myRecSet.Open " SELECT * FROM myTable", myConnection, adOpenDynamic, adLockOptimistic

    Do While Not myRecSet.EOF
    'here you can use your own special function (I use my own)
    'or MS ACCESS2000 REPLACE function
    myRecSet.fields("MemoField").Value = REPLACE(myRecSet.fields("MemoField").Value, chr(13),"")
    ' I didn't test MS Access REPLACE fce cauze I have here Access97 only.
    myRecSet.Update
    myRecSet.MoveNext
    Loop

    myRecSet.Close
    End Sub

Posting Permissions

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