Select Record based on portion of text string

[Qaspec]

I need to select records based on a portion of the value in a text string
that matches a value from another table.

I am currently using the following SQL but it is not working:

SELECT tbleSvcTickLocal.ServiceTicketID, tbleSvcTickLocal.Summary AS
Comments, tbleSvcTickLocal.CreateDt
FROM tbleSvcTickLocal
WHERE (((tbleSvcTickLocal.Summary) Like "*[Test].[Variables]*"))
GROUP BY tbleSvcTickLocal.ServiceTicketID, tbleSvcTickLocal.Summary,
tbleSvcTickLocal.CreateDt
WITH OWNERACCESS OPTION;

Please help.

 

[John Spencer]

First thing is you must have the Test table in the query. You cannot refer to
its fields if the table is not part of the query.

Second, instead of a where clause, I would use a JOIN as follows:

SELECT tbleSvcTickLocal.ServiceTicketID
, tbleSvcTickLocal.Summary AS Comments
, tbleSvcTickLocal.CreateDt
FROM tbleSvcTickLocal INNER JOIN Test
ON tbleSvcTickLocal.Summary Like "*" & [Test].[Variables] & "*"
GROUP BY tbleSvcTickLocal.ServiceTicketID, tbleSvcTickLocal.Summary,
tbleSvcTickLocal.CreateDt
WITH OWNERACCESS OPTION;

Next, instead of an aggregate query I would use DISTINCT keyword since you are
not getting the Sum, Avg, Count, etc. of any field.

SELECT DISTINCT tbleSvcTickLocal.ServiceTicketID
, tbleSvcTickLocal.Summary AS Comments
, tbleSvcTickLocal.CreateDt
FROM tbleSvcTickLocal INNER JOIN Test
ON tbleSvcTickLocal.Summary Like "*" & [Test].[Variables] & "*"
WITH OWNERACCESS OPTION;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

Try this --
WHERE tbleSvcTickLocal.Summary Like "*" & [Test].[Variables] & "*"