How to use InStr to find a fields value in text (1 Viewer)

[FAB1]

Hi

I have got to the Grrrrr:banghead: stage. I have been constructing an import routine for emails. I have worked through all the code to get the email to table etc. But I am trying to get my DB to “Read & Match ” two DB fields that will appear in the imported email Subject & or Body

1.[JobID] is always formatted in the email as JID00001 so made up some InStr code. Below is an example of the piece to read the email Body and place the value in the frm.

2. [SupplierConfirmationNo] unfortunately this field name unlike the JID will never appear in the email and it has no set formatting apart from the field’s value as a hole. As the [SupplierConfirmationNo] value is already in the DB can anybody suggest a way how can I get my DB to match them up so I can put the value in the frm like the JID

I have attached a copy of the DB with sample emails containing matching [JobID] & [SupplierConfirmationNo] examples

Hope this make some sense
Thanks
Stewart

Dim aInput() As String
Dim strLine As String
Dim strVar() As String
Dim a As Integer
Dim strTemp As String

aInput = Split(Me.Body, vbCrLf)

For a = 0 To UBound(aInput) - 1

    If Len(Nz(aInput(a))) > 1 Then
        strLine = aInput(a)
      
      If InStr(strLine, "JID") Then
         strTemp = Trim(Mid(strLine, InStr(strLine, "JID"), 8)) & " "
         strVar = Split(strLine, "JID")
         strTemp = Trim(Left(strTemp, InStr(strTemp, " ") - 1))
         If Not strTemp = "" Then Me.txtMatched JID = strTemp
            
        End If
    End If
Next a

Me.Refresh
End Sub

 

[nschroeder]

You said the two fields will appear in the email. Then you said the SupplierConfirmationNo will never appear in the email. So which is it?

 

[FAB1]

Hi nschroeder

Sorry for my poor explanation i meant to clarify the SupplierConfirmationNo as a fieldname won’t appear but its value will.

The sample data has a SupplierConfirmationNo value of 1A2B3C4 in the main tbls to “match” against the imported email tbl which has a sample record in the subject field which contains the text. Please see attached 1A2B3C4D copy of your order

Sorry for any confusion
Stewart

 

[nschroeder]

No problem Stewart. I usually need things explained at least twice before I begin to comprehend. So with that in mind, please explain how you know which JID goes with which conf no. It doesn't appear, from your db, that they are both in the same email message. You said the conf no is already in the db, but I didn't see it, other than in tbl_JobActions, and I'm assuming that is built after the fact. You probably explained it already. I just didn't see it.

 

[FAB1]

So with that in mind, please explain how you know which JID goes with which conf no.

The quickest way is if you look at the relationships in the qry_Supplier to Client Switchboard

It doesn't appear, from your db, that they are both in the same email message.

Correct the email with JobID “JID00001” examples will more likely be from a Client

The emails containing Conf No will more likely be from a supplier

You said the conf no is already in the db, but I didn't see it, other than in tbl_JobActions,

Correct the Conf No is stored in the tbl_JobActions which is linked to the tbl_Jobs then to tbl_Clients best explained in the qry_Supplier to Client Switchboard

and I'm assuming that is built after the fact. You probably explained it already. I just didn't see it.

The conf no will always be in the tbl_JobActions before the email is imported

Hope this helps

 

[nschroeder]

This seemed to work with the testing I did. I also took some liberties with your code and simplified some things, e.g., instead of handling each line in the message separately, it now handles the whole thing word-by-word. If you need it otherwise, I'm sure you can take care of it. Also, it would be more efficient to move the "Set db = CurrentDB" and "Set DB = nothing" lines to the Form_Open and Form_Close events, respectively, so they don't need to execute for each word in the message. I also added Exit For so it will quit once it finds a match.

Good luck!

Dim db As Database
Dim rsFindJobID As Recordset

Private Sub FindMatchesBT_Click()

Dim aInput() As String
Dim strWord As String
Dim a As Integer
Dim strJobIDQry As String
Dim lngJobID As Long
    
    aInput = Split(Me.Body, " ")
    Me.TestJID = ""

    For a = 0 To UBound(aInput)
        strWord = aInput(a)
        If Left(strWord, 3) = "JID" Then
            Me.TestJID = Right(strWord, Len(strWord) - 3)
            Exit For
        Else
            lngJobID = FindJobID(strWord)
            If lngJobID > 0 Then
                Me.TestJID = lngJobID
                Exit For
            End If
        End If
    Next a
    Me.Refresh
End Sub

Private Function FindJobID(strJobID) As Long
    Set db = CurrentDb()
    strJobIDQry = "SELECT DISTINCT tbl_JobActions.JID " & _
                  "From tbl_JobActions " & _
                  "WHERE tbl_JobActions.SupplierConfirmationNo=""" & strJobID & """;"
    Set rsFindJobID = db.OpenRecordset(strJobIDQry)

    If rsFindJobID.RecordCount = 0 Then
        FindJobID = 0
    Else
        FindJobID = rsFindJobID!JID
    End If
    
    Set rsFindJobID = Nothing
    Set db = Nothing
End Function

 

[FAB1]

Looks promising…but when I replaced the code nothing happened. No errors though. Could you upload the DB you got working and I can check what I have done wrong

Cheers
Stewart

 

[nschroeder]

The code currently looks at the "body" only, and the body on your form doesn't currently contain either a Job ID or Conf Number. You can test it by pasting one in. It that the problem?

 

[nschroeder]

Never mind what I just said above. I see what the problem is. In record 2, strWord apparently picked up the crlf and included it prior to the "JID0002" so the left(strWord,3) code just returned a "J". In record 4, the conf number includes a period, so it didn't find a match. If you adjust the text to avoid those issues, then it works. You'll need to find a way to trim off punctuation, special characters, etc.

 

[FAB1]

Can I check I have been using the frm_DummyData_MailMessages which is based on the sample data tblMailMessagesDummyData

It contains separate records for testing all the variables I could think of
1st JID in Subject
2nd JID in body
3rd Conf No in Subject
4th Conf No in Body
Etc

Also you will notice on that tbl I added a group of fields Excepted Results which shows the results that the code should put in the Matched results fields

Thanks
Stewart

 

[nschroeder]

Stewart,

I just wanted to make sure you got my "never mind" update above. You could use the Replace function to remove unwanted characters before you start.

strTxtVal = Replace(Me.Body, ".", "")  ' Repeat for other characters as needed
aInput = Split(strTxtVal, " ")

-Nate

 

[FAB1]

Nate....Ops sorry i posted out of sync earlier and ...cheers for the "never mind" update

When i changed the Me.TestJID over to the Me.Matched JID i was trying to figure out what to change/add to also

1. Get the code to check the Subject field for results
2. When the email has a Conf No in it could you Also get the code to put the Conf No JAID in the Matched JAID field

Thanks again for all the help so far
Stewart

 

[nschroeder]

Assuming that either the JID or the Conf No (but not both) will appear in either the subject or the body, this will check both subject & body at the same time:

    strTxtVal = Me.Subject & " " & Me.Body
    strTxtVal = Replace(strTxtVal, ".", "")  ' Repeat for other characters as needed
    strTxtVal = Replace(strTxtVal, Chr(10), "") ' remove CR
    strTxtVal = Replace(strTxtVal, Chr(13), "") ' remove LF
    aInput = Split(strTxtVal, " ")

Just adjust this line to put the result wherever you want it:

Me.TestJID = lngJobID

Also, I noticed your field names don't follow the "good practice" rules of not using spaces in field and object names. Just sayin'.
Finally, for efficiency sake, I would also move the Set db = CurrentDB & Set db = nothing lines to Form_Open and Form_Close, respectively.

Let me know how it goes.

-Nate

 

[FAB1]

Thanks again for the update

Did you have any luck with

2. When the email has a Conf No in it could you Also get the code to put the Conf No`s JAID in the Matched JAID field

as well as the JID in the Matched JID

 

[nschroeder]

That's what I was addressing in the second code window above. Unless I'm not understanding your question.

 

[FAB1]

I had already changed the Me.TestJID = lngJobID to Me.Matched JID = lngJobID

What i was looking for was the Me.Matched JAID to be populated with the Conf No`s JAID (JobActionID) from the tbl_JobActions

As an example if you look at the MessageID 7 (3rd in the set) you will see JID=1 JAID=5

Stewart

 

[nschroeder]

I see. To do that would require returning multiple values from the function, which can be done, but probably more cumbersome than needed. Instead, I converted the function to a subroutine and made some other adjustments. Here's the full code:

Option Compare Database
Dim db As Database
Dim rsFindJobID As Recordset
Dim aInput() As String
Dim strWord As String
Dim a As Integer
Dim strJobIDQry As String

Private Sub FindMatchesBT_Click()
    strTxtVal = Me.Subject & " " & Me.Body
    strTxtVal = Replace(strTxtVal, ".", "")  ' Repeat for other characters as needed
    strTxtVal = Replace(strTxtVal, Chr(10), "") ' remove CR
    strTxtVal = Replace(strTxtVal, Chr(13), "") ' remove LF
    aInput = Split(strTxtVal, " ")
    TestJID = ""

    For a = 0 To UBound(aInput)
        strWord = aInput(a)
        If Left(strWord, 3) = "JID" Then
            TestJID = Right(strWord, Len(strWord) - 3)
            Exit For
        Else
            Call FindJobID
            If Matched_JID > 0 Then
                Exit For
            End If
        End If
    Next a
    Me.Refresh
End Sub

Private Sub FindJobID()
    strJobIDQry = "SELECT DISTINCT tbl_JobActions.JID, tbl_JobActions.JAID " & _
                  "FROM tbl_JobActions " & _
                  "WHERE tbl_JobActions.SupplierConfirmationNo=""" & strWord & """;"
    Set rsFindJobID = db.OpenRecordset(strJobIDQry)

    With rsFindJobID
        If .RecordCount = 0 Then
            Matched_JID = 0
            Matched_JAID = 0
        Else
            Matched_JID = !JID
            Matched_JAID = !JAID
        End If
    End With
    
    Set rsFindJobID = Nothing
End Sub

Private Sub Form_Close()
    Set db = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
    Set db = CurrentDb()
End Sub

I would also suggest moving all the code from the Click event to the Current event. Then you could get rid of the button and the form controls would populate automatically as you move from one record to another.

Have fun.
-Nate

 

[FAB1]

So close….....Works great when the email contains a Conf No but when it’s a email that contains a JID00001 it just adds 0 values to the Matched JID & Matched JAID

 

[nschroeder]

Well, in those cases, you already know the JID, so just plug it in the form. I'm sure you can handle it. I'd hate to have all the fun!

 

[FAB1]

Ok manual it is then. Or i could just fire up my old code if Matched JID = O

Thanks again for all your help
Stewart