Solved

Deleting part of a memo field's contents

Posted on 2015-02-06
15
158 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

760 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

17 Experts available now in Live!

Get 1:1 Help Now