Issue with subquery using LIKE statement

[TUSSFC]

My SQL so far:

SELECT s1_quotes.*
FROM s1_quotes
WHERE (SELECT [Services_Desktop]![service_id]
FROM Services_Desktop
WHERE [s1_quotes]![description] LIKE "*" & [Services_Desktop]![service_id] & "*");

s1_quotes is a table with a "description" field which is memo format as it contains too many characters for a text.

Services_Desktop is a query based on a services table which filters the services to just get desktop ones. This query has a single field - "service_id".

The aim is to return a list of records in the s1_quotes table which contain any desktop service_id in the description field.

The above query/subquery is the closest I can get to date.

When I run the query it does output a list of results (which seem to match the criteria) but when it finishes outputting it returns this error message:

"At most one record can be returned by this subquery"

When I click OK all the records/fields are replaced with #Name?

I have checked the Services_Desktop query and there are NO duplicate service_id's as I know this can cause this error.

I don't think I can use a JOIN query as it involves a Memo field ... so this method seems to be the closest I've got so far. Open to suggestions!

Thanks in advance.

 

[TUSSFC]

I've changed the Services_Desktop query to return different results and it now works (without any ' either side of the *s).
So it must be something to do with the data the Services_Desktop query was returning :-s
I've tried a few different combinations of filters on the Services_Desktop query and some work and some return the error.
Hmph.

 

[TUSSFC]

Right ... next I've got rid of the "Services_Desktop" query and split the service table into 3 separate tables.
So we've got services_desktop, services_network and services_xtower.
If I amend the above query for each table ... only services_tower returns results with no errors. The other 2 give errors. Which indicates to me it's a problem with the source data in the tables.
What problem though --- I have no idea! :-(
Anyone know what issues with the source data in either the services_desktop, services_network or main s1_quotes table could cause the error msg
"At most one record can be returned by this subquery"
??
There's NO duplicate in any of the tables.

 

[TUSSFC]

Yeeeeeaaaaaahhhhhhhh!!!!!
I create 2 new queries ...
1. Just opens all fields in services_desktop and s1_quotes
2. Based on Query1 above uses the following very simplistic SQL:
SELECT *
FROM Query1
WHERE s1_quotes!description LIKE "*" & services_desktop!service_id & "*";
And it worked
Yay.