Can you help with this one?

SUM fails - Data type mismatch in criteria expretion

SELECT [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
[Last Name], Sum([Time Keeping Base Report].[Total Hours]) AS [SumOfTotal
Hours]
FROM [Time Keeping Base Report]
GROUP BY [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
[Last Name];

works ok
SELECT [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
[Last Name], [Time Keeping Base Report].[Total Hours]
FROM [Time Keeping Base Report]
GROUP BY [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
[Last Name], [Time Keeping Base Report].[Total Hours];

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1

Hi Trevor

My guess is that the field [Total Hours] is storing the numeric expression 
of the hours as a string.

Check the table specs under design mode.

Also try:

SELECT [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
[Last Name], Sum(CDbl([Time Keeping Base Report].[Total Hours])) AS 
[SumOfTotal
Hours]
FROM [Time Keeping Base Report]
GROUP BY [Time Keeping Base Report].[First Name], [Time Keeping Base 
Report].
[Last Name];

Also since in typical data sets there is far less repetition of last names 
than first names, the group by should first group on last name.

Regards

Kevin



"trevorC via AccessMonster.com" <u44860@uwe> wrote in message 
news:a26c816a7e4ba@uwe...
> Can you help with this one?
>
> SUM fails - Data type mismatch in criteria expretion
>
> SELECT [Time Keeping Base Report].[First Name], [Time Keeping Base 
> Report].
> [Last Name], Sum([Time Keeping Base Report].[Total Hours]) AS [SumOfTotal
> Hours]
> FROM [Time Keeping Base Report]
> GROUP BY [Time Keeping Base Report].[First Name], [Time Keeping Base 
> Report].
> [Last Name];
>
> works ok
> SELECT [Time Keeping Base Report].[First Name], [Time Keeping Base 
> Report].
> [Last Name], [Time Keeping Base Report].[Total Hours]
> FROM [Time Keeping Base Report]
> GROUP BY [Time Keeping Base Report].[First Name], [Time Keeping Base 
> Report].
> [Last Name], [Time Keeping Base Report].[Total Hours];
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1
> 

Perhaps you should provide the data type of [Total Hours]. When you view this 
in datasheet, it the column left or right-aligned?

Is [Time Keeping Base Report] a table or query? I can't tell from the name.

-- 
Duane Hookom
Microsoft Access MVP


"trevorC via AccessMonster.com" wrote:

> Can you help with this one?
> 
> SUM fails - Data type mismatch in criteria expretion
> 
> SELECT [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
> [Last Name], Sum([Time Keeping Base Report].[Total Hours]) AS [SumOfTotal
> Hours]
> FROM [Time Keeping Base Report]
> GROUP BY [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
> [Last Name];
> 
> works ok
> SELECT [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
> [Last Name], [Time Keeping Base Report].[Total Hours]
> FROM [Time Keeping Base Report]
> GROUP BY [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
> [Last Name], [Time Keeping Base Report].[Total Hours];
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201001/1
> 
> .
> 

Your earlier posting tells me that Total Hours is a TEXT field (column).  You 
need to the TOTAL Minutes field that you are generating in the base query.

SUM([Total Minutes])\60 as TotalHours
SUM([Total Minutes]) Mod 60 as RemainingMinutes

IF you wanted that all as hours and minutes in a string you could use the 
following expression to generate something like "69:12" - a string indicating 
69 hours and 12 minutes.

SUM([Total Minutes])\60 & Format(SUM([Total Minutes]) Mod 60,"\:00")


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

trevorC via AccessMonster.com wrote:
> Can you help with this one?
> 
> SUM fails - Data type mismatch in criteria expretion
> 
> SELECT [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
> [Last Name], Sum([Time Keeping Base Report].[Total Hours]) AS [SumOfTotal
> Hours]
> FROM [Time Keeping Base Report]
> GROUP BY [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
> [Last Name];
> 
> works ok
> SELECT [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
> [Last Name], [Time Keeping Base Report].[Total Hours]
> FROM [Time Keeping Base Report]
> GROUP BY [Time Keeping Base Report].[First Name], [Time Keeping Base Report].
> [Last Name], [Time Keeping Base Report].[Total Hours];
>