Parameters Not Quite Working

[MichelleM via AccessMonster.com]

I have a query set up with start and end date parameters. It has been
working great until this past month. The data history for this query goes
back to September 2007 and now when I enter in a week from September 2008, I
get 2007 data (of the same week listed in the parameters) with 2008. Any
suggestions on how to make sure my parameters are met? This is what I have
listed:

=[Start Date mm/dd/yyyy] And <=[End Date mm/dd/yyyy]

Thanks for any suggestions!

 

[KARL DEWEY]

Post your complete SQL statement.
--
KARL DEWEY
Build a little - Test a little

I have a query set up with start and end date parameters. It has been
working great until this past month. The data history for this query goes
back to September 2007 and now when I enter in a week from September 2008, I
get 2007 data (of the same week listed in the parameters) with 2008. Any
suggestions on how to make sure my parameters are met? This is what I have
listed:

=[Start Date mm/dd/yyyy] And <=[End Date mm/dd/yyyy]

Thanks for any suggestions!

 

[MichelleM via AccessMonster.com]

SELECT 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.
RecordingCountMonth, tbldbo_vw_Agent_Stats_New.DATEDT, tblWGLs.[Employee ID],
tbldbo_vw_Agent_Stats_New.CNAME, tbldbo_vw_Agent_Stats_New.
RecordingScoreMonth
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 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.RecordingCountMonth,
tbldbo_vw_Agent_Stats_New.DATEDT, tblWGLs.[Employee ID],
tbldbo_vw_Agent_Stats_New.CNAME, tbldbo_vw_Agent_Stats_New.
RecordingScoreMonth
HAVING (((tbldbo_vw_Agent_Stats_New.DATEDT)<=[Start Date mm/dd/yyyy] And
(tbldbo_vw_Agent_Stats_New.DATEDT)<=[End Date mm/dd/yyyy]));

Post your complete SQL statement.

I have a query set up with start and end date parameters. It has been
working great until this past month. The data history for this query goes

[quoted text clipped - 6 lines]

Thanks for any suggestions!

 

[Jerry Whittle]

I don't know exactly what you mean. Could you show some of the problem dates
returned plus what is expected?

Any other criteria?

Is the field with the parameters a Date/Time field OR a text field with
something that looks like a date?

Are the Parameters defined up in Tools, Query, Parameters?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a query set up with start and end date parameters. It has been
working great until this past month. The data history for this query goes
back to September 2007 and now when I enter in a week from September 2008, I
get 2007 data (of the same week listed in the parameters) with 2008. Any
suggestions on how to make sure my parameters are met? This is what I have
listed:

=[Start Date mm/dd/yyyy] And <=[End Date mm/dd/yyyy]

Thanks for any suggestions!

 

[KARL DEWEY]

Try changing to this ---
HAVING (((tbldbo_vw_Agent_Stats_New.DATEDT) >=[Start Date mm/dd/yyyy] And
(tbldbo_vw_Agent_Stats_New.DATEDT)<=[End Date mm/dd/yyyy]));

--
KARL DEWEY
Build a little - Test a little

SELECT 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.
RecordingCountMonth, tbldbo_vw_Agent_Stats_New.DATEDT, tblWGLs.[Employee ID],
tbldbo_vw_Agent_Stats_New.CNAME, tbldbo_vw_Agent_Stats_New.
RecordingScoreMonth
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 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.RecordingCountMonth,
tbldbo_vw_Agent_Stats_New.DATEDT, tblWGLs.[Employee ID],
tbldbo_vw_Agent_Stats_New.CNAME, tbldbo_vw_Agent_Stats_New.
RecordingScoreMonth
HAVING (((tbldbo_vw_Agent_Stats_New.DATEDT)<=[Start Date mm/dd/yyyy] And
(tbldbo_vw_Agent_Stats_New.DATEDT)<=[End Date mm/dd/yyyy]));

Post your complete SQL statement.

I have a query set up with start and end date parameters. It has been
working great until this past month. The data history for this query goes

[quoted text clipped - 6 lines]

Thanks for any suggestions!

 

[Jerry Whittle]

I also recommend putting this, including the semicolon, at the very top of
the SQL statement:

PARAMETERS [Start Date mm/dd/yyyy] DateTime, [End Date mm/dd/yyyy] DateTime;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Try changing to this ---
HAVING (((tbldbo_vw_Agent_Stats_New.DATEDT) >=[Start Date mm/dd/yyyy] And
(tbldbo_vw_Agent_Stats_New.DATEDT)<=[End Date mm/dd/yyyy]));

--
KARL DEWEY
Build a little - Test a little

SELECT 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.
RecordingCountMonth, tbldbo_vw_Agent_Stats_New.DATEDT, tblWGLs.[Employee ID],
tbldbo_vw_Agent_Stats_New.CNAME, tbldbo_vw_Agent_Stats_New.
RecordingScoreMonth
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 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.RecordingCountMonth,
tbldbo_vw_Agent_Stats_New.DATEDT, tblWGLs.[Employee ID],
tbldbo_vw_Agent_Stats_New.CNAME, tbldbo_vw_Agent_Stats_New.
RecordingScoreMonth
HAVING (((tbldbo_vw_Agent_Stats_New.DATEDT)<=[Start Date mm/dd/yyyy] And
(tbldbo_vw_Agent_Stats_New.DATEDT)<=[End Date mm/dd/yyyy]));

Post your complete SQL statement.
I have a query set up with start and end date parameters. It has been
working great until this past month. The data history for this query goes
[quoted text clipped - 6 lines]

Thanks for any suggestions!

 

[MichelleM via AccessMonster.com]

Sorry, I should have been a little more clear when I posted my question.

When I run this query, I have date parameters set up (not defined in Query
Parameters) to ask for a Start Date and an End Date. For example, I will
enter in 9/1/2008 to Start and 9/15/2008 to End. When the query returns its
results, I see data from 9/1/2007 to 9/15/2007 and 9/1/2008 to 9/15/2208.

My query is run from a view on a SQL server. The DATEDT field is a text
field and I can't change it to a date/time field because of being linked.

I don't know exactly what you mean. Could you show some of the problem dates
returned plus what is expected?

Any other criteria?

Is the field with the parameters a Date/Time field OR a text field with
something that looks like a date?

Are the Parameters defined up in Tools, Query, Parameters?

I have a query set up with start and end date parameters. It has been
working great until this past month. The data history for this query goes

[quoted text clipped - 6 lines]

Thanks for any suggestions!

 

[Jerry Whittle]

There's the rub! Your query will not return the expected records because you
are searching on a text field and expecting it to work like a date.
"9/16/2007" is not between "9/1/2008" and "9/15/2008" because text looks from
left to right. Heck if you had some records like "9/1/2011" they would show
up but "9/9/2007" probably would not.

What to do.... You need to convert the text into dates. As you say that you
can't due to it being a linked table, we'll have to try something else. In
the query up in the field put something like:

Text2Date: CDate([DATEDT])

Then put the between criteria ( defined as a date ) below that field.

One BIG problem is that the CDate function will hiccup badly if any of the
data can't be converted to a real date. The following will give you a Type
Mismatch error 13.

CDate("2/30/2008")
CDate("13/13/2008")

I highly recommend running a query like below to see if any problem records
are returned. Be sure to put in the correct table and field name:

SELECT DATEDT, IsDate([DATEDT]) AS NotDate
FROM TheTableName
WHERE (((IsDate([DATEDT]))=False));

If any problem records are returned, you either need to fix them OR deal
with them. Something like below will convert the text to a date if it can.
Otherwise it will show 1/1/1950 as the date. You could change it to some
other date.

Text2Date: IIf(IsDate([DATEDT])=True,CDate([DATEDT]),#1/1/1950#)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Sorry, I should have been a little more clear when I posted my question.

When I run this query, I have date parameters set up (not defined in Query
Parameters) to ask for a Start Date and an End Date. For example, I will
enter in 9/1/2008 to Start and 9/15/2008 to End. When the query returns its
results, I see data from 9/1/2007 to 9/15/2007 and 9/1/2008 to 9/15/2208.

My query is run from a view on a SQL server. The DATEDT field is a text
field and I can't change it to a date/time field because of being linked.

I don't know exactly what you mean. Could you show some of the problem dates
returned plus what is expected?

Any other criteria?

Is the field with the parameters a Date/Time field OR a text field with
something that looks like a date?

Are the Parameters defined up in Tools, Query, Parameters?

I have a query set up with start and end date parameters. It has been
working great until this past month. The data history for this query goes

[quoted text clipped - 6 lines]

Thanks for any suggestions!

 

[MichelleM via AccessMonster.com]

THANK YOU JERRY!

This worked perfectly!! My query is returning the expected records!

I also ran the problem query you suggested and nothing came back, but I'll
definitely keep checking that one periodically.

Thank you so much for you help and patience!!

There's the rub! Your query will not return the expected records because you
are searching on a text field and expecting it to work like a date.
"9/16/2007" is not between "9/1/2008" and "9/15/2008" because text looks from
left to right. Heck if you had some records like "9/1/2011" they would show
up but "9/9/2007" probably would not.

What to do.... You need to convert the text into dates. As you say that you
can't due to it being a linked table, we'll have to try something else. In
the query up in the field put something like:

Text2Date: CDate([DATEDT])

Then put the between criteria ( defined as a date ) below that field.

One BIG problem is that the CDate function will hiccup badly if any of the
data can't be converted to a real date. The following will give you a Type
Mismatch error 13.

CDate("2/30/2008")
CDate("13/13/2008")

I highly recommend running a query like below to see if any problem records
are returned. Be sure to put in the correct table and field name:

SELECT DATEDT, IsDate([DATEDT]) AS NotDate
FROM TheTableName
WHERE (((IsDate([DATEDT]))=False));

If any problem records are returned, you either need to fix them OR deal
with them. Something like below will convert the text to a date if it can.
Otherwise it will show 1/1/1950 as the date. You could change it to some
other date.

Text2Date: IIf(IsDate([DATEDT])=True,CDate([DATEDT]),#1/1/1950#)

Sorry, I should have been a little more clear when I posted my question.

[quoted text clipped - 20 lines]