By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,098 Members | 1,893 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,098 IT Pros & Developers. It's quick & easy.

Access - Subqueries and 'Like'

P: 7
WHERE Field10 not in (SELECT Field1 from Exception_CLIs)

works fine to only pick records that do not appear the Exception_CLIs list.

However I also want to ignore those that start with particular prefixes as well as those entered as the complete number in the list.

Is it possible to use the Like Operator to do this?
Apr 30 '06 #1
Share this Question
Share on Google+
8 Replies

P: 135
Can you give an example and information on your tables?

Does "prefix" have it's own field or would it be the left(NumberField,3) ?

Have you tried "AND" "OR" ?

As far as "LIKE", it looks for matched patterns and usually used with wildcards

"Where YourField LIKE "ST*" " would give Everything That starts with "St"

"Where YourField LIKE "*ST*" " would give Everything with "St" in it
Apr 30 '06 #2

P: 36
Something like this seems to work:

SELECT Table1.KeyField, Table1.KeyText
FROM Table1
(SELECT [Table2]![ExceptionID]
WHERE [Table1]![KeyField] LIKE [EXCEPTIONID] & "*") Is Null;
Apr 30 '06 #3

P: 7
Thanks for your help.

However this comes back with:

At most one record can be returned by this subquery.

I added the & "*" to one version of the query and it crashed Access!

The exception data is about 20 records of varying length. Some are exact matches to the data and some are the first few characters, hence the problem.
May 1 '06 #4

P: 36
Hmm, I'm not sure then. That was how I set up my test data and it worked fine for me.

I do get that same error message though if I have duplicate exceptions in the exception table (i.e. Exception XY is in the exception table twice).

Or it might also generate this error if there weren't duplicates in the exception table, but there were two or more possible matches (XY and XYZ would both be exceptions for XYZA), which is more likely what's happening.

If it's duplicates, problem solved, otherwise, you could look into using the Left function and write something like this:

SELECT [KeyField]
FROM Table1
WHERE Left([KeyField],2) Not In (SELECT LEFT( [Table2]![ExceptionID],2) FROM [Table2]);

Then you're only comparing the first two characters from each table. This would work, I think. In Table1, if you have XYZ and XY, both would return exceptions if you have an exception id of XY, but if you didn't want XYZ to return an exception then using Left() won't work.
May 1 '06 #5

P: 7
Yes, there was a duplicate that I removed and it now works!

May 1 '06 #6

P: 7
This finds the records matching full or partial:

SELECT [20060424].Field10
FROM 20060424
WHERE ((SELECT [Exception_CLIs]![Field1]
FROM Exception_CLIs
WHERE [20060424]![Field10] like ([Exception_CLIs]![Field1] & "*")))
ORDER BY field10;

I now want to find all the records excluding those in the exception list, there may be duplicates of Field10. ie Not Like.
May 1 '06 #7

P: 36
I'm a lil confused, wasn't that what you wanted to do originally? The original query should exclude anything from [20060424] where [Field10] matches or is similar to [ExceptionCLIs].[Field1]. If you change Is Null, to Is Not Null, then you'll return only the records with exceptions (although I think you might get duplicates in your resulting query, not sure). Duplicates of [Field10] didn't give me any problems before, only duplicates in the exception table.

If I'm misunderstanding, please clarify.
May 1 '06 #8

P: 1
I am trying to use LIKE in a web page and the syntax looks ok but I never get the correct results. Here is what the sql statement looks like:

Select * from Drawings where ProjectID =1 and Title LIKE "*cover*"

Can anyone give me any pointers, the database connection is fine other sql statements generated by the page works fine just not this one, well it does not give an erros just an empty recordset.

any help would be appreciated, thanks
Jan 30 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.