Hi Rizwan,
By the sound of it the contents of the Content field look like this:
Field1: Value1
Field2: Value2
Field3: Value3
Field4: Value4
Field5: Value5
Field6: Value6
If so, you can use the rgxGet() function at the bottom of this message
in calculated fields in a query. For example, this expression in a field
Field3: rgxGet(strContent, "Field3:\s*([^\x0d]*)")
would return
Value3
from the sample data above.
What the expression does is tell the function to look for the fieldname
followed by a colon (and maybe white space), and then return everything
from there up to the end of the line. [^\x0d] means any character except
a carriage return, which is 0d in hexadecimal.
What I want to do is to link my online from results, which I receive in
email, to access. And I am running into a problem:
Q- I have linked the folder which have all the result email with access. Now
it imports the results, which are 6 rows with feild name and results
separated by ":" in one field "Content". How can I import it so it imports
the feilds, which is before ":" into feilds and then the rest of data into
rows below?
thanks.
'CODE STARTS===========================================
Public Function rgxGet(Optional ByVal Target As Variant, _
Optional ByVal Pattern As String = "", _
Optional ByVal CaseSensitive As Boolean = False, _
Optional ByVal Multiline As Boolean = False, _
Optional ByVal FailOnError As Boolean = True, _
Optional ByVal Persist As Boolean = False) _
As Variant
'Regular expression matching function suitable for use
'in VB/A generally and in Access queries.
'Copyright (C)2005 John Nurick
'January 2005
'Takes a search string (Target), a regular expression
'(Pattern), and a replacement string (Replacement).
'- If a substring of Target matches Pattern, returns that substring.
'- If Pattern includes grouping parentheses, returns the substring
' matched by the first group.
'- If no match, returns empty string.
'- Returns Null on error unless FailOnError is True.
'CaseSensitive matches regardless of case.
'Multiline controls whether a Target containing
'newlines is parsed as one multiline string or a
'succession of one-line strings.
'Persist controls whether the compiled RegExp object
'remains in memory ready for the next call to the
'function or whether it is disposed of immediately. This
'means the function can be used in queries without having
'to create, compile, use and destroy
'a new RegExp object for each row being processed.
'But it also means that the object remains in memory
'after the query has run. To destroy the object and release
'the memory, call this function one last time with no arguments.
'
'Calling the function with different arguments (e.g. a new
'Pattern) recompiles the RegExp object, so
'the function can be used in different queries. However there
'may be problems if two threads are calling the function at
'the same time.
Const rgxPROC_NAME = "rgxGet"
Static oRE As Object
'Static declaration means we don't have to create
'and compile the RegExp object every single time
'the function is called.
Dim oMatches As Object
On Error GoTo ErrHandler
rgxGet = Null 'Default return value
'NB: if FailOnError is false, returns Null on error
If IsMissing(Target) Then
'This is the signal to dispose of oRE
Set oRE = Nothing
Exit Function 'with default value
End If
'Create the RegExp object if necessary
If oRE Is Nothing Then
Set oRE = CreateObject("VBScript.Regexp")
End If
With oRE
'Check whether the current arguments (other than Target)
'are different from those stored in oRE, and update them
'(thereby recompiling the regex) only if necessary.
If CaseSensitive = .IgnoreCase Then
.IgnoreCase = Not .IgnoreCase
End If
If Multiline <> .Multiline Then
.Multiline = Multiline
End If
If Pattern <> .Pattern Then
.Pattern = Pattern
End If
'Finally, execute the match
If IsNull(Target) Then
rgxGet = Null
Else
Set oMatches = oRE.Execute(Target)
If oMatches.Count > 0 Then
If oMatches(0).SubMatches.Count > 0 Then
'Pattern includes a ( ) group; return what it matched
rgxGet = oMatches(0).SubMatches(0)
Else
rgxGet = oMatches(0)
End If
Else
rgxGet = ""
End If
End If
End With
'Tidy up and normal exit
If Not Persist Then Set oRE = Nothing
Exit Function
ErrHandler:
If FailOnError Then
With Err
Select Case .Number
'Replace the default "object-defined error" message
Case 13: .Description = "Type mismatch, probably because " _
& "the ""Target"" argument could not be converted to a string"
Case 5017: .Description = "Syntax error in regular expression"
Case 5018: .Description = "Unexpected quantifier in regular
expression"
Case 5019: .Description = "Expected ']' in regular expression"
Case 5020: .Description = "Expected ')' in regular expression"
Case Else
If oRE Is Nothing Then 'Failed to create Regexp object
.Description = "Could not create VBScript.RegExp object. " &
Err.Description
Else 'Unexpected error
.Description = rgxPROC_NAME & ": " & .Description
End If
End Select
Set oRE = Nothing
.Raise Err.Number, rgxPROC_NAME, _
rgxPROC_NAME & "(): " & .Description
End With
Else 'Fail silently
Err.Clear
Set oRE = Nothing
End If
End Function
'CODE ENDS =============================================
of the query design grid you now have just