Query Parameters Not Working

[MichelleM via AccessMonster.com]

I have created a query based on tables linked to a SQL Server. The date
column in the table is a text field and I'm not able to change it because it
linked, so I used the CDate function to convert the text to a date.

My query has parameters set up on the CDate field to return only results
between the 2 dates I enter. Such as 11/1/2008 to 11/5/2008. The query used
to run perfectly, but now, (not sure what happened), It's returning results
of 11/1/2007 to 11/5/2007 and 11/1/2008 to 11/5/2008, as if the conversion is
not working anymore (this was an issue I had before implementing the CDate
function and Jerry Whittle from this site, helped me with it.)

I'm looking for any suggestions, thanks in advance!

I've included the SQL statement for my query.

SELECT CDate([DATEDT]) AS Text2Date, tbldbo_vw_Agent_Stats_New.CNAME,
tblAgents.[Employee ID], tblAgents.[First Name], tblAgents.[Last Name],
tbldbo_vw_Agent_Stats_New.AnsweredACDCallCount, tbldbo_vw_Agent_Stats_New.
CallTalkTime, tbldbo_vw_Agent_Stats_New.InboundHoldTime,
tbldbo_vw_Agent_Stats_New.CallACWTime, [CallTalkTime]+[CallACWTime]+
[InboundHoldTime] AS InboundHandleTime, tbldbo_vw_Agent_Stats_New.
OutboundCount, tbldbo_vw_Agent_Stats_New.OutboundTime,
tbldbo_vw_Agent_Stats_New.OutboundCallFollowup, tbldbo_vw_Agent_Stats_New.
OutboundHoldTime, [OutboundTime]+[OutboundCallFollowup]+[OutboundHoldTime] AS
OutboundHandleTime, tbldbo_vw_Agent_Stats_New.AnsweredACDOtherMediaCount,
tbldbo_vw_Agent_Stats_New.OtherMediaHoldTime, tbldbo_vw_Agent_Stats_New.
OtherMediaTalkTime, tbldbo_vw_Agent_Stats_New.OtherMediaACWTime,
[OtherMediaHoldTime]+[OtherMediaTalkTime]+[OtherMediaACWTime] AS
OtherMediaHandleTime, tbldbo_vw_Agent_Stats_New.TransferTo3922,
tbldbo_vw_Agent_Stats_New.AwayStatusDuration, tbldbo_vw_Agent_Stats_New.
OtherStatusDuration, tblWGLs.[Workgroup Leader], tbldbo_vw_Agent_Stats_New.
RecordingScoreMonth, tbldbo_vw_Agent_Stats_New.RecordingCountMonth, tblWGLs.
[Employee ID]
FROM tblWGLs INNER JOIN (tbldbo_vw_Agent_Stats_New INNER JOIN tblAgents ON
tbldbo_vw_Agent_Stats_New.CNAME = tblAgents.[Employee ID]) ON tblWGLs.
[Workgroup Leader] = tblAgents.[Workgroup Leader]
GROUP BY CDate([DATEDT]), tbldbo_vw_Agent_Stats_New.CNAME, tblAgents.
[Employee ID], tblAgents.[First Name], tblAgents.[Last Name],
tbldbo_vw_Agent_Stats_New.AnsweredACDCallCount, tbldbo_vw_Agent_Stats_New.
CallTalkTime, tbldbo_vw_Agent_Stats_New.InboundHoldTime,
tbldbo_vw_Agent_Stats_New.CallACWTime, [CallTalkTime]+[CallACWTime]+
[InboundHoldTime], tbldbo_vw_Agent_Stats_New.OutboundCount,
tbldbo_vw_Agent_Stats_New.OutboundTime, tbldbo_vw_Agent_Stats_New.
OutboundCallFollowup, tbldbo_vw_Agent_Stats_New.OutboundHoldTime,
[OutboundTime]+[OutboundCallFollowup]+[OutboundHoldTime],
tbldbo_vw_Agent_Stats_New.AnsweredACDOtherMediaCount,
tbldbo_vw_Agent_Stats_New.OtherMediaHoldTime, tbldbo_vw_Agent_Stats_New.
OtherMediaTalkTime, tbldbo_vw_Agent_Stats_New.OtherMediaACWTime,
[OtherMediaHoldTime]+[OtherMediaTalkTime]+[OtherMediaACWTime],
tbldbo_vw_Agent_Stats_New.TransferTo3922, tbldbo_vw_Agent_Stats_New.
AwayStatusDuration, tbldbo_vw_Agent_Stats_New.OtherStatusDuration, tblWGLs.
[Workgroup Leader], tbldbo_vw_Agent_Stats_New.RecordingScoreMonth,
tbldbo_vw_Agent_Stats_New.RecordingCountMonth, tblWGLs.[Employee ID]
HAVING (((CDate([DATEDT]))>=[Start Date] And (CDate([DATEDT]))<=[End Date]));

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Declare your parameters as dates. Also, change the HAVING to a WHERE
clause. E.g.:

PARAMETERS [Start Date] Date, [End Date] Date;
SELECT ...
FROM ...
WHERE CDate([DATEDT])>=[Start Date] And CDate([DATEDT])<=[End Date]
GROUP BY ...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSSMqJYechKqOuFEgEQKURACgoGYbxyqk6utx3i9TPVyx5qMC4rsAoMDi
VfvUWnWVIVXugKtMJJX4HhlO
=xKqO
-----END PGP SIGNATURE-----

I have created a query based on tables linked to a SQL Server. The date
column in the table is a text field and I'm not able to change it because it
linked, so I used the CDate function to convert the text to a date.

My query has parameters set up on the CDate field to return only results
between the 2 dates I enter. Such as 11/1/2008 to 11/5/2008. The query used
to run perfectly, but now, (not sure what happened), It's returning results
of 11/1/2007 to 11/5/2007 and 11/1/2008 to 11/5/2008, as if the conversion is
not working anymore (this was an issue I had before implementing the CDate
function and Jerry Whittle from this site, helped me with it.)

I'm looking for any suggestions, thanks in advance!

I've included the SQL statement for my query.

SELECT CDate([DATEDT]) AS Text2Date, tbldbo_vw_Agent_Stats_New.CNAME,
tblAgents.[Employee ID], tblAgents.[First Name], tblAgents.[Last Name],
tbldbo_vw_Agent_Stats_New.AnsweredACDCallCount, tbldbo_vw_Agent_Stats_New.
CallTalkTime, tbldbo_vw_Agent_Stats_New.InboundHoldTime,
tbldbo_vw_Agent_Stats_New.CallACWTime, [CallTalkTime]+[CallACWTime]+
[InboundHoldTime] AS InboundHandleTime, tbldbo_vw_Agent_Stats_New.
OutboundCount, tbldbo_vw_Agent_Stats_New.OutboundTime,
tbldbo_vw_Agent_Stats_New.OutboundCallFollowup, tbldbo_vw_Agent_Stats_New.
OutboundHoldTime, [OutboundTime]+[OutboundCallFollowup]+[OutboundHoldTime] AS
OutboundHandleTime, tbldbo_vw_Agent_Stats_New.AnsweredACDOtherMediaCount,
tbldbo_vw_Agent_Stats_New.OtherMediaHoldTime, tbldbo_vw_Agent_Stats_New.
OtherMediaTalkTime, tbldbo_vw_Agent_Stats_New.OtherMediaACWTime,
[OtherMediaHoldTime]+[OtherMediaTalkTime]+[OtherMediaACWTime] AS
OtherMediaHandleTime, tbldbo_vw_Agent_Stats_New.TransferTo3922,
tbldbo_vw_Agent_Stats_New.AwayStatusDuration, tbldbo_vw_Agent_Stats_New.
OtherStatusDuration, tblWGLs.[Workgroup Leader], tbldbo_vw_Agent_Stats_New.
RecordingScoreMonth, tbldbo_vw_Agent_Stats_New.RecordingCountMonth, tblWGLs.
[Employee ID]
FROM tblWGLs INNER JOIN (tbldbo_vw_Agent_Stats_New INNER JOIN tblAgents ON
tbldbo_vw_Agent_Stats_New.CNAME = tblAgents.[Employee ID]) ON tblWGLs.
[Workgroup Leader] = tblAgents.[Workgroup Leader]
GROUP BY CDate([DATEDT]), tbldbo_vw_Agent_Stats_New.CNAME, tblAgents.
[Employee ID], tblAgents.[First Name], tblAgents.[Last Name],
tbldbo_vw_Agent_Stats_New.AnsweredACDCallCount, tbldbo_vw_Agent_Stats_New.
CallTalkTime, tbldbo_vw_Agent_Stats_New.InboundHoldTime,
tbldbo_vw_Agent_Stats_New.CallACWTime, [CallTalkTime]+[CallACWTime]+
[InboundHoldTime], tbldbo_vw_Agent_Stats_New.OutboundCount,
tbldbo_vw_Agent_Stats_New.OutboundTime, tbldbo_vw_Agent_Stats_New.
OutboundCallFollowup, tbldbo_vw_Agent_Stats_New.OutboundHoldTime,
[OutboundTime]+[OutboundCallFollowup]+[OutboundHoldTime],
tbldbo_vw_Agent_Stats_New.AnsweredACDOtherMediaCount,
tbldbo_vw_Agent_Stats_New.OtherMediaHoldTime, tbldbo_vw_Agent_Stats_New.
OtherMediaTalkTime, tbldbo_vw_Agent_Stats_New.OtherMediaACWTime,
[OtherMediaHoldTime]+[OtherMediaTalkTime]+[OtherMediaACWTime],
tbldbo_vw_Agent_Stats_New.TransferTo3922, tbldbo_vw_Agent_Stats_New.
AwayStatusDuration, tbldbo_vw_Agent_Stats_New.OtherStatusDuration, tblWGLs.
[Workgroup Leader], tbldbo_vw_Agent_Stats_New.RecordingScoreMonth,
tbldbo_vw_Agent_Stats_New.RecordingCountMonth, tblWGLs.[Employee ID]
HAVING (((CDate([DATEDT]))>=[Start Date] And (CDate([DATEDT]))<=[End Date]));

 

[MichelleM via AccessMonster.com]

That worked!! Thank you so much! I'm not an SQL writer, but I'm realizing
how important it is to know SQL when working in Access. It makes things much
easier.

Declare your parameters as dates. Also, change the HAVING to a WHERE
clause. E.g.:

PARAMETERS [Start Date] Date, [End Date] Date;
SELECT ...
FROM ...
WHERE CDate([DATEDT])>=[Start Date] And CDate([DATEDT])<=[End Date]
GROUP BY ...

I have created a query based on tables linked to a SQL Server. The date
column in the table is a text field and I'm not able to change it because it

[quoted text clipped - 50 lines]

tbldbo_vw_Agent_Stats_New.RecordingCountMonth, tblWGLs.[Employee ID]
HAVING (((CDate([DATEDT]))>=[Start Date] And (CDate([DATEDT]))<=[End Date]));