Can I combine a WHERE and an INNER JOIN?

Kind of a two-part question, as I'm not sure which approach is more feasible to do what I need to do, in Access 2007.  I'm trying to find an efficient way to do the following in a single query, if possible:

- search through a table of Contracts to find those that expire within the next 4 years.
- search through the same table of Contracts to find those where a text field may contain a date string or the word(s) expire, expiration, etc.

The current query we use is only set up to search for the expiration dates, like this:

Select *
FROM CONTRACT
WHERE CONTRACT.ExpirationDate is between #5/1/2011# And #5/1/2015#

I am now trying to add the functionality of the string search.  To this end, I have set up a Date_Patterns table which contains parameters for the strings to look for.  In a previous EE thread, it was suggested I set up this table and do an INNER JOIN, like this:

Select *
FROM CONTRACT
INNER JOIN Date_Patterns
on CONTRACT.Notes = Date_Patterns.Pattern.

So.....

#1.  What I'd like to do is be able to combine these two queries into a single query.  Is that possible?

Or....

#2.  if I can't use the INNER JOIN approach in the same query, how about something like this to put it all together:

SELECT *
FROM CONTRACT
WHERE ((CONTRACT.ExpirationDate) Between #5/1/2011# And #5/1/2015#) OR  ((CONTRACT.Notes) LIKE Date_Patterns.Pattern)

Of course in this latter approach, when I run the query I am prompted to provide a value for Patterns, probably because the SELECT statement isn't selecting anything from the Date_Patterns table.  However, I don't need the Patterns in my query results, I only need to use them to compare to Contract.Notes.  Any suggestions here?
swpa_wntAsked:
Who is Participating?
 
knightEknightConnect With a Mentor Commented:
Do you mean this?


select *
FROM CONTRACT
INNER JOIN Date_Patterns
on CONTRACT.Notes = Date_Patterns.Pattern
and CONTRACT.ExpirationDate is between #5/1/2011# And #5/1/2015#
0
 
swpa_wntAuthor Commented:
Your last line:

and CONTRACT.ExpirationDate is between #5/1/2011# And #5/1/2015#


Is that supposed to be a separate WHERE clause? Or is it somehow part of the INNER JOIN, as in "INNER JOIN Statement-A and Statement-B"?
0
 
swpa_wntAuthor Commented:
OK, I tried it as a WHERE and that seemed to work.  Thanks.

I'll be posting another question about nested INNER JOINS.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.