From: Whitney on
I created a new blank query and pasted your SQL code. I'm getting data type
mismatch in criteria expression. Not sure what the issue is.

"Marshall Barton" wrote:

> I wasn't sure what fields you were really using so I just
> used place holder names. Here's my attempt to translate
> your query to waht I was saying:
>
> SELECT DateValue(tbl_Tracker.Date),
> tbl_Tracker.SLR,
> Count(*) AS CountOfSLR
> FROM tbl_Tracker
> GROUP BY DateValue(tbl_Tracker.Date), tbl_Tracker.SLR
>
> I used Count(*) instead of Count(tbl_Tracker.SLR) because *
> counts all the records even if the SLR field contains Null
> in some records and because it is a lot faster than counting
> the non null values in a field.
> --
> Marsh
> MVP [MS Access]
>
>
> Whitney wrote:
> >I don't understand your SQL code..
> >SELECT DateValue(datefield) As TheDate, person, Count(*) as Tracked
> >FROM tbl_Tracker
> >GROUP BY DateValue(datefield), person
> >
> >This is what I have now:
> >SELECT tbl_Tracker.Date, tbl_Tracker.SLR, Count(tbl_Tracker.SLR) AS CountOfSLR
> >FROM tbl_Tracker
> >GROUP BY tbl_Tracker.Date, tbl_Tracker.SLR;
> >
> >
> >"Marshall Barton" wrote:
> >> Whitney wrote:
> >> >I have a tracking table (tbl_Tracker) that now contains date and time in one
> >> >field [Date] and agent name in another field [SLR]. I would like run a query
> >> >to do a count of number of records by agent by day. How do I set up the query
> >> >to recognize only the date and not the time.
> >> >
> >> >I have Group By [SLR], Count [SLR] and Group By [Date], however it shows a
> >> >record for each date and specific time instead of grouping the whole day.
> >> >
> >> >Then I need to build another query that looks at another table
> >> >(tbl_SL_Call_Stats) for number of calls handled [Handled] by day [Date] for a
> >> >date range and compare that to the above query for the number of tracks
> >> >[Tracked:Nz ([CountOfSLR]),0) by day for each agent [SLR].
> >> >
> >> >Ex.
> >> >Date Name Handled Tracked
> >> >4/19/2010 Mary 20 15
> >> >4/19/2010 Bob 35 34
> >> >4/20/2010 Mary 29 29
> >> >4/20/2010 Bob 45 40
> >>
> >>
> >> SELECT DateValue(datefield) As TheDate,
> >> person, Count(*) as Tracked
> >> FROM tbl_Tracker
> >> GROUP BY DateValue(datefield), person
> >>
> >> Not sure I follow what the other query is supposed to be
> >> doing.
> .
>
From: Marshall Barton on
Criteria? What criteria? You never mentioned any criteria.
The least you could do is post an explanation of whatever
the criteria is supposed to do along with a Copy/Paste of
the query with this criteria.
--
Marsh
MVP [MS Access]


Whitney wrote:
>I created a new blank query and pasted your SQL code. I'm getting data type
>mismatch in criteria expression. Not sure what the issue is.
>
>"Marshall Barton" wrote:
>> I wasn't sure what fields you were really using so I just
>> used place holder names. Here's my attempt to translate
>> your query to waht I was saying:
>>
>> SELECT DateValue(tbl_Tracker.Date),
>> tbl_Tracker.SLR,
>> Count(*) AS CountOfSLR
>> FROM tbl_Tracker
>> GROUP BY DateValue(tbl_Tracker.Date), tbl_Tracker.SLR
>>
>> I used Count(*) instead of Count(tbl_Tracker.SLR) because *
>> counts all the records even if the SLR field contains Null
>> in some records and because it is a lot faster than counting
>> the non null values in a field.
>>
>>
>> Whitney wrote:
>> >I don't understand your SQL code..
>> >SELECT DateValue(datefield) As TheDate, person, Count(*) as Tracked
>> >FROM tbl_Tracker
>> >GROUP BY DateValue(datefield), person
>> >
>> >This is what I have now:
>> >SELECT tbl_Tracker.Date, tbl_Tracker.SLR, Count(tbl_Tracker.SLR) AS CountOfSLR
>> >FROM tbl_Tracker
>> >GROUP BY tbl_Tracker.Date, tbl_Tracker.SLR;
>> >
>> >
>> >"Marshall Barton" wrote:
>> >> Whitney wrote:
>> >> >I have a tracking table (tbl_Tracker) that now contains date and time in one
>> >> >field [Date] and agent name in another field [SLR]. I would like run a query
>> >> >to do a count of number of records by agent by day. How do I set up the query
>> >> >to recognize only the date and not the time.
>> >> >
>> >> >I have Group By [SLR], Count [SLR] and Group By [Date], however it shows a
>> >> >record for each date and specific time instead of grouping the whole day.
>> >> >
>> >> >Then I need to build another query that looks at another table
>> >> >(tbl_SL_Call_Stats) for number of calls handled [Handled] by day [Date] for a
>> >> >date range and compare that to the above query for the number of tracks
>> >> >[Tracked:Nz ([CountOfSLR]),0) by day for each agent [SLR].
>> >> >
>> >> >Ex.
>> >> >Date Name Handled Tracked
>> >> >4/19/2010 Mary 20 15
>> >> >4/19/2010 Bob 35 34
>> >> >4/20/2010 Mary 29 29
>> >> >4/20/2010 Bob 45 40
>> >>
>> >>
>> >> SELECT DateValue(datefield) As TheDate,
>> >> person, Count(*) as Tracked
>> >> FROM tbl_Tracker
>> >> GROUP BY DateValue(datefield), person
>> >>
>> >> Not sure I follow what the other query is supposed to be
>> >> doing.
>> .
>>

From: John Spencer on
I would guess that tbl_Tracker.Date is a date field that is sometimes null.
Or that tblTracker.Date is not a date field, but is a text field of some type
and either contains nulls or contains a value that cannot be interpreted as a
date. DateValue will generate errors if the field is Null or cannot be
interpreted as a date.

SELECT IIF(IsDate(Tbl_Tracker.Date),DateValue(tbl_Tracker.Date),Null) as
JustTheDate
, tbl_Tracker.SLR, Count(tbl_Tracker.SLR) AS CountOfSLR
FROM tbl_Tracker
GROUP BY IIF(IsDate(Tbl_Tracker.Date),DateValue(tbl_Tracker.Date),Null),
tbl_Tracker.SLR;

OR alternative would be to use a where clause to exclude the nulls if the
field is a field of the type Date.

SELECT DateValue(tbl_Tracker.Date) as JustTheDate
, tbl_Tracker.SLR
, Count(tbl_Tracker.SLR) AS CountOfSLR
FROM tbl_Tracker
WHERE tblTracker.Date is not null
GROUP BY DateValue(tbl_Tracker.Date), tbl_Tracker.SLR;

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Whitney wrote:
> I created a new blank query and pasted your SQL code. I'm getting data type
> mismatch in criteria expression. Not sure what the issue is.
> "John Spencer" wrote:
>
>> SELECT Datevalue(tbl_Tracker.Date) as JustTheDate, tbl_Tracker.SLR,
>> Count(tbl_Tracker.SLR) AS CountOfSLR
>> FROM tbl_Tracker
>> GROUP BY DateValue(tbl_Tracker.Date), tbl_Tracker.SLR;
>>
>> John Spencer
>> Access MVP 2002-2005, 2007-2010
>> The Hilltop Institute
>> University of Maryland Baltimore County
>>
>> Whitney wrote:
>>> I don't understand your SQL code..
>>> SELECT DateValue(datefield) As TheDate, person, Count(*) as Tracked
>>> FROM tbl_Tracker
>>> GROUP BY DateValue(datefield), person
>>>
>>> This is what I have now:
>>> SELECT tbl_Tracker.Date, tbl_Tracker.SLR, Count(tbl_Tracker.SLR) AS CountOfSLR
>>> FROM tbl_Tracker
>>> GROUP BY tbl_Tracker.Date, tbl_Tracker.SLR;
>>>
>>>
>>>
>>> "Marshall Barton" wrote:
>>>
>>>> Whitney wrote:
>>>>
>>>>> I have a tracking table (tbl_Tracker) that now contains date and time in one
>>>>> field [Date] and agent name in another field [SLR]. I would like run a query
>>>>> to do a count of number of records by agent by day. How do I set up the query
>>>>> to recognize only the date and not the time.
>>>>>
>>>>> I have Group By [SLR], Count [SLR] and Group By [Date], however it shows a
>>>>> record for each date and specific time instead of grouping the whole day.
>>>>>
>>>>> Then I need to build another query that looks at another table
>>>>> (tbl_SL_Call_Stats) for number of calls handled [Handled] by day [Date] for a
>>>>> date range and compare that to the above query for the number of tracks
>>>>> [Tracked:Nz ([CountOfSLR]),0) by day for each agent [SLR].
>>>>>
>>>>> Ex.
>>>>> Date Name Handled Tracked
>>>>> 4/19/2010 Mary 20 15
>>>>> 4/19/2010 Bob 35 34
>>>>> 4/20/2010 Mary 29 29
>>>>> 4/20/2010 Bob 45 40
>>>> SELECT DateValue(datefield) As TheDate,
>>>> person, Count(*) as Tracked
>>>> FROM tbl_Tracker
>>>> GROUP BY DateValue(datefield), person
>>>>
>>>> Not sure I follow what the other query is supposed to be
>>>> doing.
>>>>
>>>> --
>>>> Marsh
>>>> MVP [MS Access]
>>>> .
>>>>
>> .
>>
From: Whitney on
Like I said, I used your exact SQL code and pasted it into a blank query. I
did not specifiy any criteria.

"Marshall Barton" wrote:

> Criteria? What criteria? You never mentioned any criteria.
> The least you could do is post an explanation of whatever
> the criteria is supposed to do along with a Copy/Paste of
> the query with this criteria.
> --
> Marsh
> MVP [MS Access]
>
>
> Whitney wrote:
> >I created a new blank query and pasted your SQL code. I'm getting data type
> >mismatch in criteria expression. Not sure what the issue is.
> >
> >"Marshall Barton" wrote:
> >> I wasn't sure what fields you were really using so I just
> >> used place holder names. Here's my attempt to translate
> >> your query to waht I was saying:
> >>
> >> SELECT DateValue(tbl_Tracker.Date),
> >> tbl_Tracker.SLR,
> >> Count(*) AS CountOfSLR
> >> FROM tbl_Tracker
> >> GROUP BY DateValue(tbl_Tracker.Date), tbl_Tracker.SLR
> >>
> >> I used Count(*) instead of Count(tbl_Tracker.SLR) because *
> >> counts all the records even if the SLR field contains Null
> >> in some records and because it is a lot faster than counting
> >> the non null values in a field.
> >>
> >>
> >> Whitney wrote:
> >> >I don't understand your SQL code..
> >> >SELECT DateValue(datefield) As TheDate, person, Count(*) as Tracked
> >> >FROM tbl_Tracker
> >> >GROUP BY DateValue(datefield), person
> >> >
> >> >This is what I have now:
> >> >SELECT tbl_Tracker.Date, tbl_Tracker.SLR, Count(tbl_Tracker.SLR) AS CountOfSLR
> >> >FROM tbl_Tracker
> >> >GROUP BY tbl_Tracker.Date, tbl_Tracker.SLR;
> >> >
> >> >
> >> >"Marshall Barton" wrote:
> >> >> Whitney wrote:
> >> >> >I have a tracking table (tbl_Tracker) that now contains date and time in one
> >> >> >field [Date] and agent name in another field [SLR]. I would like run a query
> >> >> >to do a count of number of records by agent by day. How do I set up the query
> >> >> >to recognize only the date and not the time.
> >> >> >
> >> >> >I have Group By [SLR], Count [SLR] and Group By [Date], however it shows a
> >> >> >record for each date and specific time instead of grouping the whole day.
> >> >> >
> >> >> >Then I need to build another query that looks at another table
> >> >> >(tbl_SL_Call_Stats) for number of calls handled [Handled] by day [Date] for a
> >> >> >date range and compare that to the above query for the number of tracks
> >> >> >[Tracked:Nz ([CountOfSLR]),0) by day for each agent [SLR].
> >> >> >
> >> >> >Ex.
> >> >> >Date Name Handled Tracked
> >> >> >4/19/2010 Mary 20 15
> >> >> >4/19/2010 Bob 35 34
> >> >> >4/20/2010 Mary 29 29
> >> >> >4/20/2010 Bob 45 40
> >> >>
> >> >>
> >> >> SELECT DateValue(datefield) As TheDate,
> >> >> person, Count(*) as Tracked
> >> >> FROM tbl_Tracker
> >> >> GROUP BY DateValue(datefield), person
> >> >>
> >> >> Not sure I follow what the other query is supposed to be
> >> >> doing.
> >> .
> >>
>
> .
>
From: Marshall Barton on
Sorry, but I don't see how to relate that error to the query
I posted. There is nothing in the query that can get a data
type mismatch error, much less in a nonexistent criteria.

At this point, I am lost and have to wonder where you pasted
the SQL. Into a "blank query" in SQL view or some place in
a new query's design view?? Because it is rather difficult
to use text messages to explain things in the query design
grid, we normally speak queries using SQL view. If you are
using design view we are speaking different languages and
coherent communications have gone south.
--
Marsh
MVP [MS Access]


Whitney wrote:
>Like I said, I used your exact SQL code and pasted it into a blank query. I
>did not specifiy any criteria.
>
>"Marshall Barton" wrote:
>> Criteria? What criteria? You never mentioned any criteria.
>> The least you could do is post an explanation of whatever
>> the criteria is supposed to do along with a Copy/Paste of
>> the query with this criteria.