Checking a range of dates in Access

How would I check for all records where Data_A is between April 1, 2011 and December 2, 2011 and no records before April 1, 2011 exists.
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
select * from
tableX
where data_A >=#4/1/2011# and data_A <=#12/2/2011#
0
moriniaAdvanced Analytics AnalystAuthor Commented:
capricorn1

Does that query ensure that there is no record that is less than 4/2/2011 at all?  If a member has two records one 3/1/2011 and another one 5/1/2011.  I do not want to pull this member.
0
Michael CarrilloInformation Systems ManagerCommented:
Then you need a query like:

select * from
YourTable
where data_A > #4/1/2011# and data_A < #12/2/2011#
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rey Obrero (Capricorn1)Commented:
ohh, try this, you need to change  "recordID" with the actual field for the record ID

select * from
tableX
where data_A >=#4/1/2011# and data_A <=#12/2/2011#
and exists(select T.recordID from tableX where T.date_A< #4/1/2011)=false
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael CarrilloInformation Systems ManagerCommented:
If you want to exclude any member with more than one date you can do
a count on the members ID and exclude records with more than one for the count
as follows:


select MemberID, MemberName, count(*) as Ct from
Yourtable
where data_A > #4/1/2011# and data_A < #12/2/2011# and Ct=1
Group by MemberID, MemberName
0
Michael CarrilloInformation Systems ManagerCommented:
Another way to do that is:

select * from
YourTable Y
where Y.data_A > #4/1/2011# and Y.data_A < #12/2/2011#
and Y.MemberID Not in(select X.MemberID from YourTable X where X.date_A< #4/2/2011)
0
moriniaAdvanced Analytics AnalystAuthor Commented:
Capricorn1,

I have not used access in a while.  I am on Access 2007.  Where do I go to enter the SQL version of the query?
0
Michael CarrilloInformation Systems ManagerCommented:
In the query Wizard you select the SQL view.
0
Rey Obrero (Capricorn1)Commented:
open a new query
from ribbon
Create > Query Design
Close the Show Table window
click on SQL view from the ribbon or right click on the empty space for the "tables" and select SQL view
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.