Problem with a query.

[kbsudhir]

Hi All,

I have query here which substract two colums & give data in another.

SELECT Team.Names, TimeCapture.Date, TimeCapture.BreakStartTime, TimeCapture.BreakEndTime, Format(TimeCapture!BreakEndTime-TimeCapture!BreakStartTime,"HH:MM:SS") AS [Break Duration]
FROM TimeCapture INNER JOIN Team ON TimeCapture.UserID = Team.UserID;


Both BreakEndTime & BreakStartTime captures the break start timings in "Date/Time" format of the access I am making sure that only time is update
in HH:MM:SS AM/PM" format.

I named the above query as "BreakDuration_query"

Now, I want to create a query to sum the Break Duration as per the names.
For this I created the below query

SELECT BreakDuration_query.Names, Sum(BreakDuration_query.[Break Duration]) AS [SumOfBreak Duration]
FROM BreakDuration_query
WHERE (((BreakDuration_query.Date)=Now()))
GROUP BY BreakDuration_query.Names;

But I am not getting any data in the query results.

Please guide on where I am going wrong.

:banghead: :banghead: :banghead: :banghead:

Regards
Sudhir

[hansup]

Is your WHERE clause intended to limit the results to only breaks taken today?

BreakDuration_query.Date is derived from TimeCapture.Date. If TimeCapture.Date records both date and time, you may not find any which match Now(), because Now() returns the full date and time when you call it.

Maybe try revising your WHERE clause:

WHERE (((DateValue(BreakDuration_query.Date))=Date()))
I hope that points you in the right direction. If not, please tell us about the data stored in TimeCapture.Date, and what you're trying to accomplish.

Also, please consider renaming your "Date" field --- that is a reserved word in Access.

Good luck,
Hans

[JimmyTheHand]

SELECT Team.Names, TimeCapture.Date, TimeCapture.BreakStartTime, TimeCapture.BreakEndTime, Format(TimeCapture!BreakEndTime-TimeCapture!BreakStartTime,"HH:MM:SS") AS [Break Duration]
FROM TimeCapture INNER JOIN Team ON TimeCapture.UserID = Team.UserID;

Is there a reason for "!" characters in the expressions TimeCapture!BreakEndTime and TimeCapture!BreakStartTime?
I'm not sure what a ! does in a query, nevertheless, I would try with a dot instead:
TimeCapture.BreakEndTime
TimeCapture.BreakStartTime

[kbsudhir]

Thanks,

I renamed my date field & now I am getting the data for the date provided.

[kbsudhir]

Now I want to add the duration which I get after running the above query i.e query given by Jimmy...?