correctly matching a field to part of a field

Okay, so I have a table of data (let's call it tblCompleted from dbCompleted) and I want to create new records in tblCompleted based on a table in another database (let's call it tblDocuments from dbDocuments). All the records in tblDocuments have a text-type field called "ID." It's usually 4 digits (ie 1234), but some documents get revised, so sometimes ID is 1234A or 1234B, etc.

At the beginning of the Record Import procedure, I want to double-check that the ID of the current record in tblDocuments does not already exist in tblCompleted. But when the ID is pulled into tblCompleted in dbCompleted, it is not in its own field, it is simply concatenated in at the beginning of a Description field (so if ID is "1234", Description is "CR 1234: blah blah blah").

If I try to import 1234A and I use a SQL string to search for all tblCompleted records containing Like "*" & "1234A" & "*", the database would see if there was a previously existing ID 1234A, but would (correctly) overlook ID 1234. But if I'm trying to import 1234, and 1234A is already in tblCompleted, searching for Like "*" & "1234" & "*" will find 1234A, and the database will skip the import procedure, thinking (incorrectly) that the record already exists.

I tried using a different wildcard strategy, something like Like "*" & 1234[!a-c]" & "*" but that pulls up no records at all. I thought my syntax might be wrong, but ...1234[a-c]... does pull up 1234A, so I expected ...1234[!a-c]... to skip 1234A and only find 1234, instead of producing no records. :dunno

Is my criteria syntax just way out of whack? Is there a better approach to be sure that I am finding only the EXACT same ID#, and not a different rev of the same base document? I appreciate any thoughts on this, thanks!!

Are you still trying to figure this out?

Quote:

---

Originally Posted by kenhigg

Are you still trying to figure this out?

---

Yes, I'm still working on it. I understand that the syntax ...1234[!a-c]... is not working because it wants something that does have a fifth character (just not a, b, or c). But I am still sorting out how to properly tell it what I want. If I start with ID 1234, I want it to look for strings that contain ONLY 1234, no letters. If I start with ID 1234A, I want it to look for strings that contain ONLY 1234A. Any ideas on how to differentiate? Thanks!

Have you tried tack a space on the front and back end of the string?

Like "* " & "1234A" & " *"


I'm not sure if this would be the exact syntax, if not, something simular should work...

??

Quote:

---

Originally Posted by kenhigg

Have you tried tack a space on the front and back end of the string?

Like "* " & "1234A" & " *"


I'm not sure if this would be the exact syntax, if not, something simular should work...

??

---

Tacking a space onto the front, back, or front-and-back produces zero records.

Do they always appear as:

"CR 1234: blah blah blah"
"CR 1234a: blah blah blah"
"CR 1235: blah blah blah").
"CR 1236: blah blah blah").
"CR 1234b: blah blah blah").

Quote:

---

Originally Posted by kenhigg

Do they always appear as:

"CR 1234: blah blah blah"
"CR 1234a: blah blah blah"
"CR 1235: blah blah blah").
"CR 1236: blah blah blah").
"CR 1234b: blah blah blah").

---

Almost always. Well, let me rephrase that: they're SUPPOSED to. The users don't always use that format. I've been thinking that it might help if I:
(a) reformatted all existing records to have that format
(b) put some kind of default value or input mask on the field that strongly urges them to enter it in that format.

Because it might help if you always knew that you were examining the string starting from character #4 and going to either character#7 or #8.

But the problem gets worse, because not *all* the ID numbers are ####[a], only *most*. Sometimes IDs are weird things like SOP24a -- and before you ask, I and my users have no control over these ID numbers, they are given to use by outside agencies, so I unfortunately cannot standardize them. Blech.

Well.....

Then do they always have "CR " in the front and/or ": " in the back?

Weeellllll... they should, yes. They will if I reformat them and push the field into a standardized format, as I described in my post above.

Ooooh, I see, so we can search the string based on the "CR" and ":" components, regardless of how many or what characters occur between them...? That might be a great place to start...

Yeah, that's were I was headed. Let me know how it goes!

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...

So you may have:

"CR 1234"

??

On the records that don't have ": Blah blah blah", could you append a ":" to the end. This would allow the "*:" rule to find the end of the string?

Yes, that is a possible option, although I'm a little concerned that when the Description field gets carried into reports and things, it might confuse the receipient to see "CR 1234:" with nothing following the colon. It might seem as though more data was supposed to follow and was inexplicably truncated or something. I just don't want anyone at the receiving end of this data to get confused or concerned by an appended colon...

I'm going to keep playing with it, and I'll post any solutions I might come up with. Let me know if you have any more lightbulbs; you've given me so many great thoughts already, thanks!!!!