Can I combine a WHERE and an INNER JOIN?

Posted on 2011-04-25
Last Modified: 2012-05-11
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 *
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 *
INNER JOIN Date_Patterns
on CONTRACT.Notes = Date_Patterns.Pattern.


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


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

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?
Question by:swpa_wnt
    LVL 33

    Accepted Solution

    Do you mean this?

    select *
    INNER JOIN Date_Patterns
    on CONTRACT.Notes = Date_Patterns.Pattern
    and CONTRACT.ExpirationDate is between #5/1/2011# And #5/1/2015#

    Author Comment

    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"?

    Author Comment

    OK, I tried it as a WHERE and that seemed to work.  Thanks.

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


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now