Determinining Internal or external email based on domains

[frankbelly]

I'm using Access 2003. have over 14,000 rows of data for a database. Each row has a unique key DociID number. Every row contains metadata for an email or its attachment. There is only 4 columns of metadata (From, To, Cc and Bcc). I need to determine which emails are completely company internal emails based on domain names meaning emails that ONLY were sent and received by internal employees. I have concatenated the From, To, Cc, and Bcc fields for each email into a field called "AllDomains" so I have all of the email addresses into one column (concatenated for each row).

If there are any email domains in the AllDomains cell for each row that are NOT the following email domains:

@rodm
@rodmanandrenshaw

then that row of metadata contains external email addresses or non-company email addresses. If the AllDomains cell contains only email address with the above 2 domains then I know it's an internal email and it came from an internal employee and it was sent only to internal employees. Some rows will not have any metadata because they are attachments to the emails.

I would like to populate an additional column with either "Internal" or "External" every time I determine a row of metadata contains only internal or any external emails.

Any help is greatly appreciated. Thanks!

[hertfordkc]

counts the occurrences of "@" in a string and compares that to the number of sum of the occurrences of
@rodm
@rodmanandrenshaw
The function could return "internal" and "external". You would have to first check to see if the field was empty or null before using it in the INStr function.
Then use the function in a calculated field in the query containing the concatenated field.
If you need help with the VBA, let me know.

[frankbelly]

Oh that's perfect! I love that solution -- sleek and simple. Unfortunately I will need help with the VBA on it. I'm much more knowledgeable in VBA for Word and some for Excel but I'm completely new to VBA for Access and though I understand the principles, I don't know the object models at all yet.

[hertfordkc]

With your query having the concatenated field, add this field.
InExFld:testtext([ConCatFldName])

Function testtext(V) As String
Dim s As String
Dim answer As String
Dim L, M As Integer
answer = ""
If IsNull(V) Then
   answer = ""
    Else
       s = V
   If IsEmpty(V) Then
    answer = ""
        Else
          s = Replace(s, "@rodmanandrenshaw", "", 1, -1, vbBinaryCompare)
         If Len(s) = 0 Then
           answer = "Internal"
            Else
              s = Replace(s, "@rodm", "", 1, -1, vbBinaryCompare)
           If Len(s) = 0 Then
             answer = "Internal"
             Else
              answer = "External"
            End If
          End If
    
       End If
     End If
  testtext = answer
End Function

[frankbelly]

This is perfect. Thank you soooooooooooo much!