find records between two dates

  • Thread starter Thread starter Daniel Collison
  • Start date Start date
D

Daniel Collison

A dataset of patient records includes two fields that define service dates
([from_date] and [thru_date]). I want to create a subset of records that
were active on a specific date, i.e. 7/31/08. The following sql returns no
records:

SELECT [qry Dataset].originl_recipient_id, [qry Dataset].from_date, [qry
Dataset].thru_date
FROM [qry Dataset]
WHERE ((([qry Dataset].from_date)>=#7/31/2008#) AND (([qry
Dataset].thru_date)<=#7/31/2008#));

Any suggestions?
 
Did you intend to find the records where the from_date field is on or
*before* July 31, and the thru_date field is on or after?

WHERE (([qry Dataset].from_date <= #7/31/2008#)
AND ([qry Dataset].thru_date >= #8/1/2008#));

Could there be a time-component in these fields?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...
 
SELECT [qry Dataset].originl_recipient_id, [qry Dataset].from_date, [qry
Dataset].thru_date
FROM [qry Dataset]
WHERE ((([qry Dataset].from_date)<=#7/31/2008#)
AND (([qry Dataset].thru_date)>=#7/31/2008#));

The above query assumes that your date fields do not contain a time component
(other than midnight). If there is a time component then you might need
something like:

SELECT [qry Dataset].originl_recipient_id, [qry Dataset].from_date, [qry
Dataset].thru_date
FROM [qry Dataset]
WHERE ((([qry Dataset].from_date)<=#7/31/2008#)
AND (([qry Dataset].thru_date)>#7/30/2008#));


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks...this was helpful!

John Spencer said:
SELECT [qry Dataset].originl_recipient_id, [qry Dataset].from_date, [qry
Dataset].thru_date
FROM [qry Dataset]
WHERE ((([qry Dataset].from_date)<=#7/31/2008#)
AND (([qry Dataset].thru_date)>=#7/31/2008#));

The above query assumes that your date fields do not contain a time component
(other than midnight). If there is a time component then you might need
something like:

SELECT [qry Dataset].originl_recipient_id, [qry Dataset].from_date, [qry
Dataset].thru_date
FROM [qry Dataset]
WHERE ((([qry Dataset].from_date)<=#7/31/2008#)
AND (([qry Dataset].thru_date)>#7/30/2008#));


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Daniel said:
A dataset of patient records includes two fields that define service dates
([from_date] and [thru_date]). I want to create a subset of records that
were active on a specific date, i.e. 7/31/08. The following sql returns no
records:

SELECT [qry Dataset].originl_recipient_id, [qry Dataset].from_date, [qry
Dataset].thru_date
FROM [qry Dataset]
WHERE ((([qry Dataset].from_date)>=#7/31/2008#) AND (([qry
Dataset].thru_date)<=#7/31/2008#));

Any suggestions?
Click to expand...
Click to expand...
 
Thanks...this was helpful! DC

Allen Browne said:
Did you intend to find the records where the from_date field is on or
*before* July 31, and the thru_date field is on or after?

WHERE (([qry Dataset].from_date <= #7/31/2008#)
AND ([qry Dataset].thru_date >= #8/1/2008#));

Could there be a time-component in these fields?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
A dataset of patient records includes two fields that define service dates
([from_date] and [thru_date]). I want to create a subset of records that
were active on a specific date, i.e. 7/31/08. The following sql returns
no
records:

SELECT [qry Dataset].originl_recipient_id, [qry Dataset].from_date, [qry
Dataset].thru_date
FROM [qry Dataset]
WHERE ((([qry Dataset].from_date)>=#7/31/2008#) AND (([qry
Dataset].thru_date)<=#7/31/2008#));

Any suggestions?
Click to expand...
Click to expand...
 
Back
Top