find records between two dates

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

 

[Allen Browne]

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]...

 

[John Spencer]

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 Collison]

Thanks...this was helpful!

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

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?

 

[Daniel Collison]

Thanks...this was helpful! DC

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?