• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

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.
0
morinia
Asked:
morinia
  • 4
  • 3
  • 2
3 Solutions
 
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
 
macarrillo1Commented:
Then you need a query like:

select * from
YourTable
where data_A > #4/1/2011# and data_A < #12/2/2011#
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
 
macarrillo1Commented:
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
 
macarrillo1Commented:
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
 
macarrillo1Commented:
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
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now