Solved

Using an update query based on text and date

Posted on 2014-11-10
3
114 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

792 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