Error when try to sum text converted to minutes 1

[kentwoodjean]

Below is my SQL where I converted text to minutes/seconds. I thought that as a result of this I would be able to sum the totals of the minutes/seconds to get total minutes used. When I try to Sum on a separate query, I get the Data Type Mismatch in Criteria message. What might be missing?

SELECT [ToDate]-2 AS Weekending, tbl_MAIN2071iwrwek.Ext, tbl_MAIN2071iwrwek.Duration, [LastName] & "," & [FirstName] AS NAME, 1440*CDate([Duration]) AS IWR
FROM tbl_MAIN2071iwrwek
GROUP BY [ToDate]-2, tbl_MAIN2071iwrwek.LastName, tbl_MAIN2071iwrwek.FirstName, tbl_MAIN2071iwrwek.Ext, tbl_MAIN2071iwrwek.Duration;

 

[dhookom]

Are we supposed to see minutes/seconds someplace in your SQL? I assume you are attempting to convert the Duration field. Could you provide some sample records?

Is it possible that Duration might be Null?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kentwoodjean]

Yes, it is the duration field that I converted. Here is how it looks prior to and after conversion:

Duration IWR
0:00:09 0.15
0:01:20 1.33333333333333
0:02:13 2.21666666666667
0:02:15 2.25
0:05:22 5.36666666666667
0:36:30 36.5

There should not be a null entry but about the time I say that, there will be one.

 

[dhookom]

What you have shown looks fine as long as there are no nulls. We are in the dark regarding "When I try to Sum on a separate query". What separate query? Does your IWR field/column display left or right aligned?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[kentwoodjean]

I was trying to use a separate query to group by person the total amount of time used over a weeks period of time. One person could have 20 duration entries in a week and I would like to be able to total their time as well. Therefore, I used the sum feature but that didn't work.

Without using a separate query, I also tried to use the report wizard to sort by weekending, individual and then a sum of their time. I don't get the some option.

 

[PHV]

Something like this ?
SELECT ToDate-2 AS Weekending, LastName & "," & FirstName AS NAME, Sum(1440*CDate([Duration])) AS IWR
FROM tbl_MAIN2071iwrwek
GROUP BY ToDate, LastName, FirstName

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kentwoodjean]

Yes, but I get a data type mismatch.

 

[PHV]

And this ?
SELECT ToDate-2 AS Weekending, LastName & "," & FirstName AS NAME, Sum(1440*CDate([Duration])) AS IWR
FROM tbl_MAIN2071iwrwek
WHERE IsDate([Duration])
GROUP BY ToDate, LastName, FirstName

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kentwoodjean]

It is amazing!!Works beautifully. You don't know how many months I have avoided adding this on reports because I could never get it to work right. Using the IsDate is new to me.

Thanks for your assistance.