Trouble with Date field when combining 2 tables

[MichelleM via AccessMonster.com]

I created a query from one table (it's actually linked to a view in a SQL
server called vw_Agent_Stats_new) regarding stats for a customer service rep
in a call center. Now I have a table in Access that I created (Adherence)
with one other piece of data not on the SQL Server and I'm trying to get the
2 tables to work together and I'm running into issues with the dates.

The date field on the vw_Agent_Stats_new view had to be converted from text
to date using CDate and the Adherence table date column is already a
date/time field. When I run the query for the dates I specify, such as for
10/20 and 10/21, the adherence displays both 10/20 and 10/21 for the rest of
data on 10/20 only and does this for every agent, therefore, doubling my
results. Here is my SQL statement for the query.

I know this must sound very confusing, I tried to explain it the best that I
could without snapshots.

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT CDate([DATEDT]) AS Text2Date, 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], tblWGLs.[Employee ID],
tbldbo_vw_Agent_Stats_New.RecordingScoreMonth, tbldbo_vw_Agent_Stats_New.
RecordingCountMonth, tblAdherence.Date, tblAdherence.Adherence
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]) INNER JOIN tblAdherence ON
tblAgents.[Employee ID] = tblAdherence.[Login ID]
GROUP BY CDate([DATEDT]), 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], tblWGLs.[Employee ID], tbldbo_vw_Agent_Stats_New.
RecordingScoreMonth, tbldbo_vw_Agent_Stats_New.RecordingCountMonth,
tbldbo_vw_Agent_Stats_New.DATEDT, tblAdherence.Date, tblAdherence.Adherence
HAVING (((CDate([DATEDT]))>=[Start Date] And (CDate([DATEDT]))<=[End Date])
AND ((tblWGLs.[Employee ID])=[Enter WGL Employee ID]) AND ((tblAdherence.Date)

=[Start Date] And (tblAdherence.Date)<=[End Date]));

 

[tkelley via AccessMonster.com]

Yep, confusing. Just remember that SQL Server and Access treat and store
dates differently. Are you creating an expression for both to force them to
the same date format using the "format()" fucntion before joining?

I created a query from one table (it's actually linked to a view in a SQL
server called vw_Agent_Stats_new) regarding stats for a customer service rep
in a call center. Now I have a table in Access that I created (Adherence)
with one other piece of data not on the SQL Server and I'm trying to get the
2 tables to work together and I'm running into issues with the dates.

The date field on the vw_Agent_Stats_new view had to be converted from text
to date using CDate and the Adherence table date column is already a
date/time field. When I run the query for the dates I specify, such as for
10/20 and 10/21, the adherence displays both 10/20 and 10/21 for the rest of
data on 10/20 only and does this for every agent, therefore, doubling my
results. Here is my SQL statement for the query.

I know this must sound very confusing, I tried to explain it the best that I
could without snapshots.

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT CDate([DATEDT]) AS Text2Date, 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], tblWGLs.[Employee ID],
tbldbo_vw_Agent_Stats_New.RecordingScoreMonth, tbldbo_vw_Agent_Stats_New.
RecordingCountMonth, tblAdherence.Date, tblAdherence.Adherence
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]) INNER JOIN tblAdherence ON
tblAgents.[Employee ID] = tblAdherence.[Login ID]
GROUP BY CDate([DATEDT]), 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], tblWGLs.[Employee ID], tbldbo_vw_Agent_Stats_New.
RecordingScoreMonth, tbldbo_vw_Agent_Stats_New.RecordingCountMonth,
tbldbo_vw_Agent_Stats_New.DATEDT, tblAdherence.Date, tblAdherence.Adherence
HAVING (((CDate([DATEDT]))>=[Start Date] And (CDate([DATEDT]))<=[End Date])
AND ((tblWGLs.[Employee ID])=[Enter WGL Employee ID]) AND ((tblAdherence.Date)

=[Start Date] And (tblAdherence.Date)<=[End Date]));

 

[MichelleM via AccessMonster.com]

No, I'm not. How would I do that? I am not experienced in SQL, so please be
as specific as possible.

Yep, confusing. Just remember that SQL Server and Access treat and store
dates differently. Are you creating an expression for both to force them to
the same date format using the "format()" fucntion before joining?

I created a query from one table (it's actually linked to a view in a SQL
server called vw_Agent_Stats_new) regarding stats for a customer service rep

[quoted text clipped - 55 lines]

AND ((tblWGLs.[Employee ID])=[Enter WGL Employee ID]) AND ((tblAdherence.Date)

=[Start Date] And (tblAdherence.Date)<=[End Date]));

 

[tkelley via AccessMonster.com]

Of course I have no way to test, but start with this:

HAVING (((CDate(format([DATEDT],"mm/dd/yy")))>=[Start Date] And (format(
[DATEDT],"mm/dd/yy"))<=[End Date])
AND ((tblWGLs.[Employee ID])=[Enter WGL Employee ID]) AND ((format
(tblAdherence.Date,"mm/dd/yy"))

=[Start Date] And (format(tblAdherence.Date,"mm/dd/yy"))<=[End Date]));

If this doesn't change anything, it's possible that it isn't related to the
dates, but maybe to the joins instead.

No, I'm not. How would I do that? I am not experienced in SQL, so please be
as specific as possible.

Yep, confusing. Just remember that SQL Server and Access treat and store
dates differently. Are you creating an expression for both to force them to

[quoted text clipped - 5 lines]

AND ((tblWGLs.[Employee ID])=[Enter WGL Employee ID]) AND ((tblAdherence.Date)
=[Start Date] And (tblAdherence.Date)<=[End Date]));