Can I use a "Like" stmt in an append query?

[KerryL]

I'm trying to use an append query to add data to table, but it doesn't seem to work when I use "Like" and wildcards in the criteria box.

The criteria used to determine what data is added to each new record is based on a text field that can contain multiple acronyms. Therefore, I can't run the query based on an exact match.

Is there a restriction against using "Like" and/or wildcards in an append query/innerjoin?

If so, I have my answer as to why it's not working. If not, perhaps I can post my query and get some help?

Thank you,
KerryL

 

[PHV]

perhaps I can post my query
Sure, with input samples and expected result.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KerryL]

Here's an example of the the query.

INSERT INTO tblContactCats ( ContactID, CategoryID )
SELECT tblContacts.ContactID, tblCategories.CategoryID
FROM tblContacts LEFT JOIN tblCategories ON tblContacts.Category = tblCategories.CategoryName
WHERE (((tblContacts.Category) Like "*CMF*"));


What happens is that when a record contains more than "CMF", the CategoryID from tblCategories is not appended into tblContactCats. It appends the CategoryID (10) for records that contain ONLY "CMF" just fine. But if the criteria field contains "CMF ABC" it adds a record to tblContactCats but leaves the CategoryID field blank.

However, I was able to get it to work by forcing the query to add an expression in there. For instance, since CMF is CategoryID #10, I appended "10" as an expression instead of having the query pull tblCategories.CategoryID (see below) and it worked just fine.

INSERT INTO tblContactCats ( ContactID, CategoryID )
SELECT tblContacts.ContactID, 10 AS Expr1
FROM tblContacts LEFT JOIN tblCategories ON tblContacts.Category = tblCategories.CategoryName
WHERE (((tblContacts.Category) Like "*CMF*"));


Thank you for your help. Please feel free to offer any other advice that comes to mind.

Merry Christmas!
KerryL

 

[PHV]

And does "CMF ABC" exists in tblCategories ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KerryL]

No, "CMF ABC" does not exist as a valid category. "CMF" and "ABC" exist as separate, valid categories. Therefore, separate entries for the Contact must be created in tblContactCats for each of the valid categories.

FYI, I built the append query to import a Filemaker Pro (ugh) file into my database, and the way FmP created the records put the text of each contact's category/categories into one field.

So in FmP the category field for 'XYZ Charity' might be: "CMF ABC Salvation Army" but in Access I'll need three separate records to link 'XYZ Charity' to "CMF", "ABC", and "Salvation Army" individually.

Hope that explains it better. Thank you for your help.