Re: SQL - Capture Specific Text and Copy to New Column/row

Well, the E definitely should not be omitted, since the function is a custom function that is designed to handle wild cards. InStr is a built-in VBA function but it does NOT handle wild cards. It treats wildcard characters as characters in the string.

Did you copy the code to a VBA module and save the module with a name other than the name of any function? Did you then compile the code and see if you got any errors?

"I am unable to get the script to work" is unfortunately not very helpful in diagnosing the problem. Did you get any error messages? OR did Access just quit? Or did you get the wrong results? OR ????



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


RichW wrote:
John,

Thank you for having replyied to my previous post and having provided an excellent suggestion. Unfortunately, I have tried the script you had suggested and for some reason I am unable to get it to work.

I suspect that the "e" preceeding "Instr" should be omitted, but I still cannot get the script to work.

Do you have any other suggestions?

Thanks again...much appreciated.

Rich

"John Spencer" wrote:

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


.

Relevant Pages

  • Re: MD5 Hash with single quote = grief in dao.findfirst
    ... option compare database is an Access ... option - it picks up the database sort order. ... > Function H70Check(strHash As String) As Long ...
    (microsoft.public.access.modulesdaovba)
  • Re: SQL - Capture Specific Text and Copy to New Column/row
    ... I have been able to create a script which captures only those rows/columns that have the string ... Please advise of how I may capture only the date/time data. ... It treats wildcard characters as characters in the string. ... The case sensitivity is set by the Option Compare setting of the module. ...
    (microsoft.public.access.modulesdaovba)
  • Re: SQL - Capture Specific Text and Copy to New Column/row
    ... The script is: ... Please advise of how I may capture only the date/time data. ... wildcard characters as characters in the string. ... by the Option Compare setting of the module. ...
    (microsoft.public.access.modulesdaovba)
  • Re: SQL - Capture Specific Text and Copy to New Column/row
    ... If you knew the exact length of the string you ... by the Option Compare setting of the module. ... Option Compare Database can only be used within Microsoft Access. ... An example of the text I wish to capture and which is contained in the ...
    (microsoft.public.access.modulesdaovba)
  • Re: SQL - Capture Specific Text and Copy to New Column/row
    ... That will be close, but you may have some extraneous characters at the end of the string. ... The case sensitivity is set by the Option Compare setting of the module. ... Option Compare Database can only be used within Microsoft Access. ... An example of the text I wish to capture and which is contained in the current table is: ...
    (microsoft.public.access.modulesdaovba)