Okay, I started working on a procedure using InStr and Mid and all this stuff to search the string, but some fields don't have the ":" because some fields don't have the ID followed by the "blah blah blah."
I'm starting to wonder if we were thinking too complicated. Your suggestion of the " *" was really good, it just wasn't quite enough by itself, because some fields end with the ID number and are followed by nothing (as opposed to a space). So what if we try...
strSQLcr = "SELECT tblMonthlyInputs.DeliverableorEvent,
tblMonthlyInputs.EventDescription, " _
& "tblMonthlyInputs.EmployeeName From tblMonthlyInputs WHERE " _
& "(((tblMonthlyInputs.DeliverableorEvent)=""CR Review"") AND " _
& "((tblMonthlyInputs.EventDescription) Like ""*"" & " & strID & " & "" *"")) " _
& "OR (((tblMonthlyInputs.EventDescription) Like ""*"" & " & strID & "));"
...the key part being Like ""*"" & " & strID & " & "" *"" OR Like ""*"" & " & strID
That way you find the whole ID followed by a space or the whole ID followed by nothing...
Aw, crud, but what about records where the ID is followed immediately by a colon? Ok, well, it may need to be a 3-part "OR"... but if I can ascertain that ALL data WILL definitely conform to one of those three scenarios, then that might be the easiest way to handle it...
Reply With Quote