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

Trouble using Like Operator.....having a dumb friday

100+
P: 219
I've got a table which contains some items that I need to compare against another table which contains a more detailed look at the items, and there could be multiple.

Example:

Table1:
Field: ACETAMINOPHEN

Table2:
Fields: ACETAMINOPHEN
ACETAMINOPHEN CHEWABLE
ACETAMINOPHEN DROPS
ACETAMINOPHEN SUPP
ACETAMINOPHEN SUSP
ACETAMINOPHEN W/COD #3
ACETAMINOPHEN W/COD ELIX
ACETAMINOPHEN W/COD LIQ 15ML

I want to be able to run a query which would look at ACETAMINOPHEN and retrieve all values from Table2 where the field is like ACETAMINOPHEN

Here is my query I'm using which returns 0 results:

Expand|Select|Wrap|Line Numbers
  1. SELECT TABLE1!PRIMARY_NAME AS OS_DRUG
  2. FROM TABLE1, TABLE2
  3. WHERE ((([TABLE2]![DETAILEDDRUG]*) Like [TABLE1]![PRIMARY_NAME]))
  4. ORDER BY TABLE1!PRIMARY_NAME;
  5.  
I tried using the below as well, but get no results.

Expand|Select|Wrap|Line Numbers
  1. SELECT TABLE1!PRIMARY_NAME AS OS_DRUG
  2. FROM TABLE1, TABLE2
  3. WHERE ((([TABLE2]![DETAILEDDRUG] + '*') Like [TABLE1]![PRIMARY_NAME]))
  4. ORDER BY TABLE1!PRIMARY_NAME;
  5.  
Apr 24 '09 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,271
You don't say exactly what the fields are called and I suspect what you describe as field values are actually different records, but try this :
Expand|Select|Wrap|Line Numbers
  1. SELECT TABLE1.PRIMARY_NAME AS OS_DRUG,
  2.        TABLE2.DETAILEDDRUG AS DTL_DRUG
  3.  
  4. FROM   TABLE1 LEFT JOIN TABLE2
  5.   ON   (TABLE2.DETAILEDDRUG LIKE '*' & TABLE1.PRIMARY_NAME & '*')
  6.  
  7. ORDER BY TABLE1.PRIMARY_NAME
Apr 24 '09 #2

100+
P: 219
Excellent, that worked like a charm, I appreciate your help!!!!!
Apr 24 '09 #3

NeoPa
Expert Mod 15k+
P: 31,271
Very pleased to hear it :)

Did you follow what and why though?
Apr 24 '09 #4

Post your reply

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