Solved

Using an update query based on text and date

Posted on 2014-11-10
3
112 Views
Last Modified: 2014-11-10
I have a memo field that might have the following text (mixed in with other text):

PT Notified of DS office
PT Notified of DS office (MM-DD-YY)
PT Notified of DS office (MM/DD/YY)

I would like to remove this from the memo field.  I have found a way to do so for the text portion but not the date portion.  (Replace [Notes],"*PT Notified of DS office*,"")

Is it possible to something like this and then trim the results when there is a date?
0
Comment
Question by:thandel
  • 2
3 Comments
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 40434292
You could run two different queries, one without a trailing date and the other with a trailing date.  You would need to run the WITH DATE query first.

The first query's Where clause would be:
Where Notes like "*PT Notified of DS office (##-##-##)*"
OR Notes like "*PT Notified of DS office (##/##/##)*"

A simplified version of the Where clause might be:
Where Notes like "*PT Notified of DS office (##[/-]##[/-]##)*"
or
Where Notes like "*PT Notified of DS office (##[-/]##[-/]##)*"

The actual update would need to use the Instr() and Mid() functions and maybe the Left() function.  It gets messy.

I think you have a problem with your Replace() function.  It should be:
Set [Notes] = Replace([Notes],"PT Notified of DS office", "")

Open in new window


It seemed easier to write a function to do the replace:
Option Explicit

Public Function Q_28554797(ByVal parmNote As String, ByVal parmPattern As String, ByVal parmReplacePattern As String) As String
    Static oRE As Object
    Static strPattern As String
    
    If oRE Is Nothing Then
        Set oRE = CreateObject("vbscript.regexp")
        oRE.Global = True
        oRE.Pattern = parmPattern
        strPattern = parmPattern
    End If
    
    If strPattern = parmPattern Then
    Else
        oRE.Pattern = parmPattern
        strPattern = parmPattern
    End If
    
    If oRE.test(parmNote) Then
        Q_28554797 = oRE.Replace(parmNote, parmReplacePattern)
    Else
        Q_28554797 = parmNote
    End If
    
End Function

Open in new window


This is how you would use it in an update query.
Set Notes = Q_28554797(notes,"PT Notified of DS office \(\d\d(?:-|/)\d\d(?:-|/)\d\d\) ?", "")

Open in new window

0
 

Author Comment

by:thandel
ID: 40434305
I made this into an SQL query but doesn't replace the desired text.  Can I not implement it in this manner?

UPDATE TPatient SET TPatient.Notes = Replace([notes],"PT Notified of DS office \(\d\d(?:-|/)\d\d(?:-|/)\d\d\) ?","")
WHERE (((TPatient.Notes) Like "*PT Notified of DS office*"));
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40434311
You can't use regular expression patterns with the Access/VB Replace() function.
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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

863 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

24 Experts available now in Live!

Get 1:1 Help Now