CallDate in query

Here is the SQL code:

SELECT [IBCCP Referral].[Caller ID], DCount("[Caller ID]","IBCCP
Referral","[Caller ID] <= " & [Caller ID]) Mod 10 AS Expr1, [IBCCP
Referral].CallDate, [IBCCP Referral].CallTaker, [IBCCP
Referral].ProviderNameNumber, [IBCCP Referral].HowHear, [IBCCP
Referral].[IRIS Referral Number], [IBCCP Referral]![First Name] & " "
& [IBCCP Referral]![Middle Initial] & " " & [IBCCP Referral]![Last
Name] AS FullName, [IBCCP Referral].LanguagePreferenceID, [IBCCP
Referral].Age, [IBCCP Referral].HomePhone, [IBCCP Referral].WorkPhone,
[IBCCP Referral].CellPhone, [IBCCP Referral].Address, [IBCCP
Referral].City, [IBCCP Referral].Zip, [IBCCP Referral].[County Code
ID], [IBCCP Agencies].Name, [IBCCP Referral].RoutineScreening, [IBCCP
Referral].Comments, [IBCCP Referral].QualityAssuarnce
FROM [IBCCP Referral] INNER JOIN [IBCCP Agencies] ON [IBCCP
Referral].AgencyID = [IBCCP Agencies].ID
GROUP BY [IBCCP Referral].[Caller ID], DCount("[Caller ID]","IBCCP
Referral","[Caller ID] <= " & [Caller ID]) Mod 10, [IBCCP
Referral].CallDate, [IBCCP Referral].CallTaker, [IBCCP
Referral].ProviderNameNumber, [IBCCP Referral].HowHear, [IBCCP
Referral].[IRIS Referral Number], [IBCCP Referral]![First Name] & " "
& [IBCCP Referral]![Middle Initial] & " " & [IBCCP Referral]![Last
Name], [IBCCP Referral].LanguagePreferenceID, [IBCCP Referral].Age,
[IBCCP Referral].HomePhone, [IBCCP Referral].WorkPhone, [IBCCP
Referral].CellPhone, [IBCCP Referral].Address, [IBCCP Referral].City,
[IBCCP Referral].Zip, [IBCCP Referral].[County Code ID], [IBCCP
Agencies].Name, [IBCCP Referral].RoutineScreening, [IBCCP
Referral].Comments, [IBCCP Referral].QualityAssuarnce
HAVING (((DCount("[Caller ID]","IBCCP Referral","[Caller ID] <= " &
[Caller ID]) Mod 10)=0) AND (([IBCCP Referral].CallDate) Between
[Forms]![frmIBCCPLabelsReports]![txtQAStart] And [Forms]!
[frmIBCCPLabelsReports]![txtQAStop]) AND (([IBCCP
Referral].QualityAssuarnce)=False))
ORDER BY [IBCCP Referral].[Caller ID];

I just do not get why the field, CallDate or rather query does not
return any records? The criteria for field, CallDate, in the above
query is:

Between [Forms]![frmIBCCPLabelsReports]![txtQAStart] And [Forms]!
[frmIBCCPLabelsReports]![txtQAStop]

Thanks,

Zufie

Re: CallDate in query

zufie wrote:

> Here is the SQL code:
>
> SELECT [IBCCP Referral].[Caller ID], DCount("[Caller ID]","IBCCP
> Referral","[Caller ID] <= " & [Caller ID]) Mod 10 AS Expr1, [IBCCP
> Referral].CallDate, [IBCCP Referral].CallTaker, [IBCCP
> Referral].ProviderNameNumber, [IBCCP Referral].HowHear, [IBCCP
> Referral].[IRIS Referral Number], [IBCCP Referral]![First Name] & " "
> & [IBCCP Referral]![Middle Initial] & " " & [IBCCP Referral]![Last
> Name] AS FullName, [IBCCP Referral].LanguagePreferenceID, [IBCCP
> Referral].Age, [IBCCP Referral].HomePhone, [IBCCP Referral].WorkPhone,
> [IBCCP Referral].CellPhone, [IBCCP Referral].Address, [IBCCP
> Referral].City, [IBCCP Referral].Zip, [IBCCP Referral].[County Code
> ID], [IBCCP Agencies].Name, [IBCCP Referral].RoutineScreening, [IBCCP
> Referral].Comments, [IBCCP Referral].QualityAssuarnce
> FROM [IBCCP Referral] INNER JOIN [IBCCP Agencies] ON [IBCCP
> Referral].AgencyID = [IBCCP Agencies].ID
> GROUP BY [IBCCP Referral].[Caller ID], DCount("[Caller ID]","IBCCP
> Referral","[Caller ID] <= " & [Caller ID]) Mod 10, [IBCCP
> Referral].CallDate, [IBCCP Referral].CallTaker, [IBCCP
> Referral].ProviderNameNumber, [IBCCP Referral].HowHear, [IBCCP
> Referral].[IRIS Referral Number], [IBCCP Referral]![First Name] & " "
> & [IBCCP Referral]![Middle Initial] & " " & [IBCCP Referral]![Last
> Name], [IBCCP Referral].LanguagePreferenceID, [IBCCP Referral].Age,
> [IBCCP Referral].HomePhone, [IBCCP Referral].WorkPhone, [IBCCP
> Referral].CellPhone, [IBCCP Referral].Address, [IBCCP Referral].City,
> [IBCCP Referral].Zip, [IBCCP Referral].[County Code ID], [IBCCP
> Agencies].Name, [IBCCP Referral].RoutineScreening, [IBCCP
> Referral].Comments, [IBCCP Referral].QualityAssuarnce
> HAVING (((DCount("[Caller ID]","IBCCP Referral","[Caller ID] <= " &
> [Caller ID]) Mod 10)=0) AND (([IBCCP Referral].CallDate) Between
> [Forms]![frmIBCCPLabelsReports]![txtQAStart] And [Forms]!
> [frmIBCCPLabelsReports]![txtQAStop]) AND (([IBCCP
> Referral].QualityAssuarnce)=False))
> ORDER BY [IBCCP Referral].[Caller ID];
>
> I just do not get why the field, CallDate or rather query does not
> return any records? The criteria for field, CallDate, in the above
> query is:
>
> Between [Forms]![frmIBCCPLabelsReports]![txtQAStart] And [Forms]!
> [frmIBCCPLabelsReports]![txtQAStop]
>
> Thanks,
>
> Zufie

A couple or three of reasons come to mind. The first is that you are
storing Now() type data to CallDate and your search is for the same day.
Between 1/1/2008 and 1/1/2008.

Another is to use Left, not Inner, joins for debugging.

Another is that all records that would be selected have a
QualityAssuarnce = True.

Why don't you trim the number of fields for output down a tad, then
remove the Totals and make it a Select statement, then change to Left
joins and then start trimming your filters. Sooner or later you'll find
the problem.

Life Will Go On
http://www.youtube.com/watch?v=CdmskOlFEbQ&NR=1