find records with related id and search for keywords within records

[live2ride]

i open tickets on our website and once those tickets are done i get an email stating it has been completed or cancelled or whatever else there is
these tickets are also in my access db (access 2007)
i have outlook linked to access and i need to search outlook table with for specific ticket numbers and once i found those records i need to search for specific keywords



something like this
search tblOutlook.Subject for number located in tblTickets.TicketNumbers, and if that record is found, i need to search tblOutlook.Subject and tblOutlook.Contents for keywords such as "Completed" or "Cancelled"

any help would be greatly appreciate

[June7]

How do you want the 'specific ticket numbers' to be determined? Will user input a ticket number into a textbox and that value should be used as criteria to search the outlook table? That should be simple.

SELECT * FROM outlooktable WHERE Subject LIKE "*" & Forms!formname!textboxname & "*" AND Contents LIKE "*Completed*";

[live2ride]

ticket numbers are added to table via form and are located in tblTickets which have 2 field TicketNumber and Status. so what im trying to do is search "Subject" field in tblOutlook and if that record exits then look for status in "subject" or "Contents" of tblOutlook because sometimes we get status in subject if email is automated or status is in "Contents" of email if email was not automated. so lets say i got email with subject "request 12345 closed", i want update tblTickets and set status to "closed" where ticketNumber = 12345

[June7]

So you don't have a specific ticket to search for? You want the code to search for ALL tickets from tblTickets and update status for any found in the Outlook table?

Please be specific about the exact behavior you want the code to perform.

[live2ride]

correct, there is no specific ticket to search for

[June7]

That does get complicated. Could there be more than one email for the same ticket number with different status indicated? Do you want to delete the emails after search or just mark as 'read'?

[live2ride]

there could be more than one email for same ticket number but with different status, as for what happens to emails after the records are updated, doesnt matter, they can be deleted or marked as "read"

[June7]

Try something like this:

Query1
SELECT tblTickets.TicketNumber, IIf(Not IsNull(DLookUp("Subject","tblOutlook","Subject LIKE '*Closed*' AND Subject LIKE '*" & [TicketNumber] & "*'")),"Closed",Null) AS SubjectClosed, IIf(Not IsNull(DLookUp("Contents","tblOutlook","Contents LIKE '*Closed*' AND Subject LIKE '*" & [TicketNumber] & "*'")),"Closed",Null) AS ContentsClosed
FROM tblTickets;

UPDATE Query1 INNER JOIN tblTickets ON Query1.TicketNumber = tblTickets.TicketNumber SET tblTickets.Status = Nz([Query1].[SubjectClosed],[Query1].[ContentsClosed])

This requires consistency in the status types that can be used. My example looks for only one status value and assumes a ticket Status will be either null or 'closed'. The more status categtories, the more complicated. At some point, VBA code might have to be used.

[live2ride]

i figured a way how to extract info but i can only do it through vba on click of button, can you help me make this code a function and update statement i can call?
with below combination i should be able to extract almost everything
so lets say i have a subject "request 12345 closed"
Dim leftSpace As Integer
Dim rightSpace As Integer
Dim length As Integer

leftSpace = InStr(1, Me.Subject, " ")
rightSpace= InStr(leftSpace + 1, Me.Subject, " ")
length = rightSpace - leftSpace

MsgBox Mid(Me.Subject, leftSpace, length)

by making this a function i think i can figure out the rest as long as i stay on the same record
another thing is when outlook folder is imported unfortunately it doesnt have a primary key so im not even sure how i would stay on that record while running functions, any ideas?

[June7]

VBA code could do an extract but I don't see how the code you show will work unless you can count on the ticket number or the word 'closed' to always be in the same position in the Subject or Contents strings. If position is not consistent code will be much more complicated.

Did you try my suggested queries?

[live2ride]

i did try your queries and they are working great thank you, i realized that emails contain so much more data which i could extract from so i was leaning towards functions but i still have long ways to go
thanks for all your help