Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Deleting part of a memo field's contents

Posted on 2015-02-06
15
Medium Priority
?
167 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 39

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 1000 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 1000 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 31

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

609 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