Solved

Deleting part of a memo field's contents

Posted on 2015-02-06
15
159 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 34

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 - Access MVP) 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

914 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now