Re: SQL - Capture Specific Text and Copy to New Column/row
- From: John Spencer <spencer@xxxxxxxxx>
- Date: Fri, 23 May 2008 12:53:07 -0400
Perhaps you can use the following custom VBA function to get you started. I've just been working on this and have not fully tested it, but perhaps it will help you get started. If you knew the exact length of the string you could add that into the mid.
SELECT Mid([Email Body],
eInstr([Email Body],"REFERENCES: ??? */*/#### *:## ?M")+ Len("References: "), 23) As DateTimeString
FROM MyTableName
WHERE (MyTableName.[Email Body]) Like '*REFERENCES: ??? */*/#### *:## ?M*';
That will be close (if it works), but you may have some extraneous characters at the end of the string.
The case sensitivity of LIKE is dependent on the settings of the module that contains this function or of the database itself. The case sensitivity is set by the Option Compare setting of the module.
Option Compare Database
Option Compare Binary
Option Compare Text
The Option Compare statement specifies the string comparison method (Binary, Text, or Database) for a module. If a module doesn't include an Option Compare statement, the default text comparison method is Binary.
Option Compare Database can only be used within Microsoft Access. This results in string comparisons based on the sort order determined by the locale ID of the database where the string comparisons occur.
If you want to guarantee a case insensitive search add this to the top of the module
'==========================================
Option Compare Text
Option Explicit
'==========================================
Public Function eInStr(StrIn, StrFind, _
Optional Start As Long = 1) As Integer
'strIn: String to search - allows nulls,
'converts non-strings to strings if possible
'strFind: String to find in StrIn
'Start: Look for match from this point on
'Returns a number indicating where strFind is located in strIn
Dim i As Long 'Loop counter
Dim p As Long 'Value to return
If Len(StrFind & "") = 0 Or Len(StrIn & "") = 0 Then
p = 0
Else
' If Start < 1 Then Start = 1 'Handle invalid start point
For i = Start To Len(StrIn & "")
If Mid(StrIn, i) Like StrFind & "*" Then
p = i
Exit For
End If
Next i
End If
eInStr = p
End Function
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
RichW wrote:
I have a table that contains an email body in a single column/row. I need to capture specific text from the email body, then drop that text into a new column..
An example of the text I wish to capture and which is contained in the current table [Email Body] is:
"....REFERENCES: Thu 5/8/2008 4:46 PM...."
I created the following SQL which successfully captures those rows that have the noted text in it:
SELECT [Email Body]
FROM MyTableName
WHERE (MyTableName.[Email Body]) Like '*REFERENCES: ??? */*/#### *:## ?M*';
Like I mentioned, the SQL noted above is succesful in capturing and displaying only those rows that contain the string "REFERENCES: ??? */*/#### *:## ?M"
But, I wish to capture a portion of that string (??? */*/#### *:## ?M) and store it into a new column, so that the new column only displays, for example, Thu 5/8/2008 4:46 PM.
Please help.
Thank you,
Rich
- Follow-Ups:
- Prev by Date: RE: Populate Outlook Contact form with Access data
- Next by Date: RE: How can I close access in vba code?
- Previous by thread: RE: access window control
- Next by thread: Re: SQL - Capture Specific Text and Copy to New Column/row
- Index(es):
Relevant Pages
|
