Replace "wildcard" string in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good morning and thank you for taking a moment to help me with this problem:

I have a column in my query with notes in it. I need to remove the date and
time information from those notes. My text looks like the following in the
field called NOTES:

review comments`1/13/2006 6:34:07 PM` Hour change
`1/11/2006 4:46:01 PM` Hour change

This information may appear anywhere in the text of that field. How can I
accomplish this in my query in another field? I want to keep the first NOTES
column with the dates and times in there.

THX cs
 
So, what you want to do is to remove all the date and time text in the field
and copy the results to a new field. Is that correct?

Current field reads
review comments`1/13/2006 6:34:07 PM` Hour change `1/11/2006 4:46:01 PM`
Hour change

New field would read
review comments Hour change Hour change

Is that correct? If so, I must say I cannot think of a way to do this
without using a function. IF the ` marks are consistently there to indicate
a date/time, you could write a VBA function using the split function to
build a string.

AIR CODE FUNCTION --- UNTESTED

Public Function CommentTextOnly (StrIN as Variant) as Variant
Dim strArray as Variant, i as integer
Dim strReturn as String

If Len(StrIN & vbnullString) = 0 Then
'Handle Null and zero-length strings
CommentTextOnly = StrIN
Else
strArray=Split(strIn,"`")
For i = Lbound(strArray) to UBound(strArray)
If IsDate(Trim(strArray(i)))=False then
strReturn = strArray(i) & strReturn
End If
Next i
CommentTextOnly = strReturn
End Function
 
Actually, those were 2 lines of data. They should end up as follows:

1) review comments`1/13/2006 6:34:07 PM` Hour change
New 1) review comments Hour change
2) `1/11/2006 4:46:01 PM` Hour change
New 2) Hour change

I'll give your "air code" function a try and see if it fixes the issue.
 
It works well in most cases. However it seems to be changing the order of the
first part from the last part when the date drops in at the middle of the
note.

Old) Work on GW TM`1/17/2006 10:39:16 AM` Hour change from 0 to 1 hours on Mon
New) Hour change from 0 to 1 hours on MonWork on GW TM
EXPECTED: Work on GW TM Hour change from 0 to 1 hours on Mon

How can we fix that?
 
Hi Cydney,

Access's built-in tools aren't much help in situations like this. I find
the simplest approach is to use the VBScript regular expression engine,
which does very powerful pattern matching and can easily be used in
Access VBA custom functions.

Here's a skeletal function that you can call in an update a query. It
takes a string (Target) and a regular expression (Pattern), and returns
the string after deleting any substring(s) that match the pattern. Paste
the function into a standard module in your database:

Public Function DeleteRegex(Target As Variant, _
Pattern As String) As Variant

Dim oRE As Object 'VBScript_RegExp_55.RegExp

If IsNull(Target) Then 'return Null if given Null
DeleteRegex = Null
Exit Function
End If

Set oRE = CreateObject("Vbscript.RegExp")
With oRE
.Global = True 'replace all matches of Pattern
.IgnoreCase = True
.Multiline = False
.Pattern = Pattern
DeleteRegex = .Replace(CStr(Target), "")
End With
End Function

This pattern:
`\d{1,2}/\d{2}/\d{4} \d{1,2}:\d\d:\d\d [AP]M`
matches the date/time substrings in your sample data. If you want to
replace the "Hour change" parts as well, it's
`\d{1,2}/\d{2}/\d{4} \d{1,2}:\d\d:\d\d [AP]M` Hour change

So if the existing field is Notes, and you want to put the notes without
dates into another field Comments, you would use an Update query to
update Notes to

DeleteRegex([Notes], "`\d{1,2}/\d{2}/\d{4} \d{1,2}:\d\d:\d\d [AP]M`")
 
This one worked perfectly!
Thank you very much John.



John Nurick said:
Hi Cydney,

Access's built-in tools aren't much help in situations like this. I find
the simplest approach is to use the VBScript regular expression engine,
which does very powerful pattern matching and can easily be used in
Access VBA custom functions.

Here's a skeletal function that you can call in an update a query. It
takes a string (Target) and a regular expression (Pattern), and returns
the string after deleting any substring(s) that match the pattern. Paste
the function into a standard module in your database:

Public Function DeleteRegex(Target As Variant, _
Pattern As String) As Variant

Dim oRE As Object 'VBScript_RegExp_55.RegExp

If IsNull(Target) Then 'return Null if given Null
DeleteRegex = Null
Exit Function
End If

Set oRE = CreateObject("Vbscript.RegExp")
With oRE
.Global = True 'replace all matches of Pattern
.IgnoreCase = True
.Multiline = False
.Pattern = Pattern
DeleteRegex = .Replace(CStr(Target), "")
End With
End Function

This pattern:
`\d{1,2}/\d{2}/\d{4} \d{1,2}:\d\d:\d\d [AP]M`
matches the date/time substrings in your sample data. If you want to
replace the "Hour change" parts as well, it's
`\d{1,2}/\d{2}/\d{4} \d{1,2}:\d\d:\d\d [AP]M` Hour change

So if the existing field is Notes, and you want to put the notes without
dates into another field Comments, you would use an Update query to
update Notes to

DeleteRegex([Notes], "`\d{1,2}/\d{2}/\d{4} \d{1,2}:\d\d:\d\d [AP]M`")


Good morning and thank you for taking a moment to help me with this problem:

I have a column in my query with notes in it. I need to remove the date and
time information from those notes. My text looks like the following in the
field called NOTES:

review comments`1/13/2006 6:34:07 PM` Hour change
`1/11/2006 4:46:01 PM` Hour change

This information may appear anywhere in the text of that field. How can I
accomplish this in my query in another field? I want to keep the first NOTES
column with the dates and times in there.

THX cs
Click to expand...
Click to expand...
 
Reverse the order of concatenation in the line.

strReturn = strArray(i) & strReturn

Should be changed to
strReturn = strReturn & strArray(i)
 
Back
Top