Hi

I've got a crazy problem with connecting Outlook to an Access database.
Basically I want to collect client's ID Details, email addresses & 'phone numbers from incoming emails, use the ID details to find their record in an Access database and then update the contact details.

Everything was going great... Could parse the data items from the body of the email but the SQL doesn't find the matching record (which does exist). The same SQL will find the record in Access - which I find really confusing. The HTML form only allows the user to enter the 1st 3 characters of both their family name and first name so Thomas Smith would enter "Tho" & "Smi".

I have set references to MS ActiveX Data Objects 2.1 library.

Is there something wrong with the SQL?
Are there any other references I should set?

Thanks for any tips.

BQ

----------------------------------------------
The code...

Option Explicit
Sub UpdatePatContacts()
' Extract contact details from emails and update the Contacts Database
' First dimension the variables for the OUTLOOK work
Dim objOL As Outlook.Application
Dim objItem As Object
Dim ThisMessageBody As String
Dim ThisWholeName As String, ThisPracNo As String, ThisSName As String, ThisCName As String, _
ThisStrDoB As String, ThisDoB As Date, ThisEmail As String, ThisTelNo As String, ThisMobPhone As String
'
' Now declare the variabels for the DATABASE work
Dim adoConn As ADODB.Connection
Dim adoRS As ADODB.Recordset
Dim ThisSQL As String, ThisDBName As String, ThisUser As String, ThisPass As String
'
' On Error Resume Next
Set objOL = Application
Set objItem = objOL.ActiveExplorer.Selection(1)
If objItem Is Nothing Then
MsgBox "There is no message selected"
Exit Sub
End If
'
' Get variables from the incoming email
Debug.Print "-----------------------"
ThisMessageBody = objItem.Body
ThisSQL = ""
ThisPracNo = ParseTextLinePair(ThisMessageBody, "Practice No")
If ThisPracNo > "" Then ThisSQL = "(EMISNo = " & ThisPracNo & ") AND " ' THIS BIT WORKS
Debug.Print ThisPracNo
Debug.Print ThisSQL
ThisWholeName = ParseTextLinePair(ThisMessageBody, "Name")
If ThisWholeName > "" Then
ThisSName = Left(ThisWholeName, InStr(ThisWholeName, " ") - 1)
ThisCName = Trim(Mid(ThisWholeName, Len(ThisSName) + 1))
'
' When name details are passed the following is added to the ThisSql, it will not bring back the required record!!!
'
ThisSQL = ThisSQL & "(SName like """ & ThisSName & "*"") AND (CName like """ & ThisCName & "*"") AND " ' <<<<<<<<<<<<<<
'
Debug.Print ThisSQL
End If
ThisStrDoB = ParseTextLinePair(ThisMessageBody, "DoB")
ThisDoB = CDate(ThisStrDoB)
If ThisDoB > Date Then ThisDoB = DateAdd("y", -100, ThisDoB)
ThisSQL = ThisSQL & "DoB =#" & Month(ThisDoB) & "/" & Day(ThisDoB) & "/" & Year(ThisDoB) & "# AND "
ThisSQL = Left(ThisSQL, Len(ThisSQL) - 5)
Debug.Print ThisStrDoB
Debug.Print CDate(ThisStrDoB)
Debug.Print ThisSQL
ThisEmail = ParseTextLinePair(ThisMessageBody, "Email")
ThisTelNo = ParseTextLinePair(ThisMessageBody, "Telephone")
ThisMobPhone = ParseTextLinePair(ThisMessageBody, "Mobile")
'
ThisSQL = "SELECT * FROM ContactDetails WHERE (" & ThisSQL & ");"
'
Debug.Print ThisSQL
ThisDBName = "whatever.mdb"
GetConn adoConn, adoRS, ThisSQL, ThisDBName
If adoRS.EOF Then
MsgBox "No matching record found for " & vbCrLf & ThisSQL
Debug.Print "No matching record found!"
Else
Debug.Print "[" & adoRS("sname") & "]"
End If
Debug.Print "-----------------------"
'Close the recordset & connection
adoRS.Close
adoConn.Close
Set adoRS = Nothing
Set adoConn = Nothing
End Sub