MS Access query

[Guest]

I am trying to select a distinct records from an access db within a certain
date range.

This is my query:

SELECT DISTINCTROW [Patient Information].[Patient First Name], [Patient
Information].[Patient Last Name], [Patient Information].[Cardiac Rehab
Program ID], First([Patient Sign In].[Date]) AS [First Of Date], Count(*) AS
[Count Of Patient Sign In], [Patient Information].[Notes]
FROM [Patient Information] INNER JOIN [Patient Sign In] ON [Patient
Information].[Cardiac Rehab ID]=[Patient Sign In].[Cardiac Rehab ID]
GROUP BY [Patient Information].[Patient First Name], [Patient
Information].[Patient Last Name], [Patient Information].[Cardiac Rehab
Program ID], [Patient Information].[Notes]
HAVING (((First([Patient Sign In].Date)) between [Type The Beginning Date:]
and [Type The Ending Date:]));

When I specify the date range of 1/10/05 to 1/12/05 I only get records from
1/10 and 1/11 even though there are records for 1/12.

Please advise what is wrong???? Thank you very much

 

[Douglas J. Steele]

Odds are that you're using Now to populate the date fields, so that they
include times. In order to get records for Jan 12th, you're going to have to
search for records before Jan 13th. Fortunately, you can do this without
making your users key in a different date:

SELECT DISTINCTROW [Patient Information].[Patient First Name], [Patient
Information].[Patient Last Name], [Patient Information].[Cardiac Rehab
Program ID], First([Patient Sign In].[Date]) AS [First Of Date], Count(*)
AS
[Count Of Patient Sign In], [Patient Information].[Notes]
FROM [Patient Information] INNER JOIN [Patient Sign In] ON [Patient
Information].[Cardiac Rehab ID]=[Patient Sign In].[Cardiac Rehab ID]
GROUP BY [Patient Information].[Patient First Name], [Patient
Information].[Patient Last Name], [Patient Information].[Cardiac Rehab
Program ID], [Patient Information].[Notes]
HAVING (((First([Patient Sign In].Date)) between [Type The Beginning
Date:]
and DateAdd("d", 1, [Type The Ending Date:]));


BTW, you really should rename your fields from Date: that's a reserved word,
and using reserved words can lead to considerable problems.