Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Deleting part of a memo field's contents

Posted on 2015-02-06
15
161 Views
Last Modified: 2015-02-06
I have a 2003 Access DB. There is a table (tMain) that has a memo field (Notes).

I would like to run through the entire DB and remove part of the memo fields contents "PT notified of DS office"

I am not familiar with DAO but modified some code that  I found but its not working.

        Case "test"
            Dim rsD As DAO.Recordset
            Set rsD = CurrentDb.OpenRecordset("tMain")
         
            Do While Not rsD.EOF
                rsD.Edit
                rsD!Notes = Replace(rsD!Notes, "PT Notifed of DS office", "")
                rsD.Update
                rsD.MoveNext
            Loop
         
            rsD.Close
            Set rsD = Nothing


Any suggestions?  Is this even possible?
0
Comment
Question by:thandel
  • 5
  • 5
  • 2
  • +3
15 Comments
 
LVL 35

Expert Comment

by:PatHartman
ID: 40594496
Please define "not working".  This looks like part of a larger procedure.  Are you sure the procedure is being executed?  Are you getting an error message?  Is the string not being replaced?

A more efficient method would be to use an update query.  They are typically faster than a code loop.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 250 total points
ID: 40594503
what exactly is not working ?
Meanwhile try this:

Dim rsD As DAO.Recordset
            Set rsD = CurrentDb.OpenRecordset("tMain", dbOpenDynaset)
         
            Do
                rsD.Edit
                rsD!Notes = Replace(rsD!Notes, "PT Notifed of DS office", "")
                rsD.Update
                rsD.MoveNext
            Loop Until rsD.EOF
         
            rsD.Close
            Set rsD = Nothing
0
 

Author Comment

by:thandel
ID: 40594528
I assume EE are so good they could look at the code and see the error... ok you are not super human.  :)

It was stopping on the line:   Set rsD = CurrentDb.OpenRecordset("tMain")
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 57
ID: 40594535
There's nothing wrong with the code you posted....it should work.  So as Pat said, define not working.

Jim.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 40594536
Instead of this:

  Set rsD = CurrentDb.OpenRecordset("tMain")

Do:

 Dim db as DAO.Database

 Set db = CurrentDB()
 Set rsD = db.OpenRecordset("tMain")

Jim.
0
 
LVL 75
ID: 40594538
"It was stopping on the line:"
is there an error ?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40594547
Remove Case "test" and try the code.
0
 

Author Comment

by:thandel
ID: 40594639
Still stoping on Set rsD = db.OpenRecordset("tMain")... tMain is the table, that is correct right?

...but I realized I had the wrong table!  I fixed that.. now I think some memo fields can be null and now I'm getting an error when that is the case.  What is the best way to work with a null memo field?
0
 

Expert Comment

by:Cardiacmont
ID: 40594655
To test for the memo field being null use the "nz" function

 rsD!Notes = Replace(nz(rsD!Notes,""), "PT Notifed of DS office", "")
0
 

Author Comment

by:thandel
ID: 40594667
BAM!  That did it... can I add a trim to that:

rsD!Notes = Trim(Replace(Nz(rsD!Notes, ""), "PT Notified of DS office", ""))

Any issues with that ?
0
 
LVL 75
ID: 40594670
Dim rsD As DAO.Recordset
            Set rsD = CurrentDb.OpenRecordset("tMain", dbOpenDynaset)
         
            Do
                rsD.Edit
                 If not IsNull(rsD!Notes) Then  rsD!Notes = Replace(rsD!Notes, "PT Notifed of DS office", "")
                rsD.Update
                rsD.MoveNext
            Loop Until rsD.EOF
         
            rsD.Close
            Set rsD = Nothing
0
 
LVL 75
ID: 40594677
"Any issues with that ?"
YES ... you are now replacing a Null with an Empty Length String ("") ... and you really do not want to do that. Just test for Null first ... as I have shown above.

mx
0
 

Author Comment

by:thandel
ID: 40594820
Right got it.  I would also like to trim it to after replacing (if not null)  Can that be incorporated?
0
 
LVL 75
ID: 40594877
Trim sure ...

  If not IsNull(rsD!Notes) Then  rsD!Notes = Trim(Replace(rsD!Notes, "PT Notifed of DS office", ""))
0
 

Author Comment

by:thandel
ID: 40594883
Cool thanks
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question