Solved

Deleting part of a memo field's contents

Posted on 2015-02-06
15
163 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
[X]
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
  • 5
  • 5
  • 2
  • +3
15 Comments
 
LVL 37

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

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

Jim.
0
 
LVL 58

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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