Having trouble adding a where clause into this query

[Substance]

Hi all,

I wrote this query to show how many records had been completed by individual people in certain time frames but I'm having trouble adding a where clause,

The query runs fine without any where clause but if I try to add "WHERE DateCompleted = #01/06/2016#" or anything along those lines it always returns no results.

I've attached the query below, any help would be appreciated.

Thanks

SELECT StaffLog.Name, StaffLog.LogIn, StaffLog.LogOut, Sum(IIf([DateCompleted]>=[LogIn] And ([DateCompleted]<=[LogOut]),1,0)) 
FROM StaffLog LEFT JOIN Items ON StaffLog.Name = Items.Name 
GROUP BY StaffLog.Name, StaffLog.LogIn, StaffLog.LogOut

 

[Joe4]

In Aggregate Queries, the WHERE clause goes before the GROUP BY clause, and the HAVING clause comes after the GROUP BY clause.
Both are criteria, though WHERE applies to data BEFORE the aggregation, where HAVING occurs afterwards (so if you wanted to check the SUM).

So I think your code should look like:

SELECT StaffLog.Name, StaffLog.LogIn, StaffLog.LogOut, Sum(IIf([DateCompleted]>=[LogIn] And ([DateCompleted]<=[LogOut]),1,0)) 
FROM StaffLog LEFT JOIN Items ON StaffLog.Name = Items.Name 
[FONT=Verdana]WHERE DateCompleted = #01/06/2016#[/FONT]
GROUP BY StaffLog.Name, StaffLog.LogIn, StaffLog.LogOut

 

[james_lankford]

or maybe even

SELECT 
  StaffLog.Name, 
  StaffLog.LogIn, 
  StaffLog.LogOut, 
  count(*) as NumberOf
FROM 
  StaffLog 
  LEFT JOIN 
    Items 
      ON 
      StaffLog.Name = Items.Name 
WHERE 
(  
  (
    DateCompleted = #01/06/2016#
  )
  and 
  (
    [DateCompleted] >= [LogIn] And [DateCompleted] <= [LogOut]
  )
)
GROUP BY 
  StaffLog.Name, 
  StaffLog.LogIn, 
  StaffLog.LogOut

 

[PichaiTC]

Be sure that #01/06/2016# means o1/Jun/2016 or 06/Jan/2016
I found that Access (in my region) prefer to interpret date as mm/dd/yyyy.

So I will use #yyyy/mm/dd# of #dd/mmm/yyyy# when pass the date parameter.

 

[Joe4]

Be sure that #01/06/2016# means o1/Jun/2016 or 06/Jan/2016
I found that Access (in my region) prefer to interpret date as mm/dd/yyyy.

So I will use #yyyy/mm/dd# of #dd/mmm/yyyy# when pass the date parameter.

Good point. Regional settings can affect that.

 

[xenou]

This query looks suspicious. Can you provide some sample data and what you expect? For instance, are login's and logout's always on the same day or always on different days? What are you trying to sum?

 

[Substance]

Thanks for the help but I'm still having trouble,

In Aggregate Queries, the WHERE clause goes before the GROUP BY clause, and the HAVING clause comes after the GROUP BY clause.
Both are criteria, though WHERE applies to data BEFORE the aggregation, where HAVING occurs afterwards (so if you wanted to check the SUM).

So I think your code should look like:

SELECT StaffLog.Name, StaffLog.LogIn, StaffLog.LogOut, Sum(IIf([DateCompleted]>=[LogIn] And ([DateCompleted]<=[LogOut]),1,0)) 
FROM StaffLog LEFT JOIN Items ON StaffLog.Name = Items.Name 
[FONT=Verdana]WHERE DateCompleted = #01/06/2016#[/FONT]
GROUP BY StaffLog.Name, StaffLog.LogIn, StaffLog.LogOut

Thank you, this was the first thing I tried but it always returns 0 results.

or maybe even

SELECT....

Thanks, I can't see why this wouldn't work but alas, I still get 0 results when trying to run it.

Be sure that #01/06/2016# means o1/Jun/2016 or 06/Jan/2016
I found that Access (in my region) prefer to interpret date as mm/dd/yyyy.

So I will use #yyyy/mm/dd# of #dd/mmm/yyyy# when pass the date parameter.

This is also something I have considered, the records are stored as dd/mm/yyyy but I have tried both 01/06/2016 and 06/01/2016 and neither return results.

This query looks suspicious. Can you provide some sample data and what you expect? For instance, are login's and logout's always on the same day or always on different days? What are you trying to sum?

Because of the sensitive information I couldn't do this without making up a ton of bogus information. Sorry, I even had to change the field/table names in my original query. I can say though that the login and logout dates should always be on the same day, it's basically when a user has opened and closed the associated userform. and the datecompleted will be when the user submitted the record they were working on,

I would like to return a count of all records where the time completed falls between the log in and log out dates for a certain staff member("Name field") when I run the query with no Where/Having clause it returns a ton of results, but when I try to filter by 1 staff member or 1 date it returns 0.

Once again, thanks for the help all.

 

[Joe4]

Change your DateCompleted field to a date format that also shows time. Does it show a time component other than 12:00 AM?
If so, that is why it is not returning anything, as you would need to remove the time component before checking to see if it exactly equals a certain date that has no time component.

 

[xenou]

Is DateCompleted, LogIn, and Logout all fields in the Table? Can you provide at least two "bogus" records to test with? You really do not need a ton of dummy data - just a very small amount of it.

would like to return a count of all records where the time completed falls between the log in and log out dates for a certain staff member("Name field")

One reason I ask is that if you are seeking records where datecompleted falls between login and logout you don't need datecompleted in the where clause (by itself). It would be helpful to have some test data in order to construct a proper query (more like what James posted than what you originally posted, if that helps - but with only one criterion, not two). Also would be useful to know the data types of the fields for DateCompleted, Login, and Logout.

 

[james_lankford]

Change your DateCompleted field to a date format that also shows time. Does it show a time component other than 12:00 AM?
If so, that is why it is not returning anything, as you would need to remove the time component before checking to see if it exactly equals a certain date that has no time component.

excellent point !

if DateCompleted contains the time then it could be something like
#01/06/2016 10:42:25 AM#

and
#01/06/2016# does not equal #01/06/2016 10:42:25 AM#
so that would never match and always return zero rows

you have to do something like

SELECT 
  StaffLog.Name, 
  StaffLog.LogIn, 
  StaffLog.LogOut, 
  count(*) as NumberOf
FROM 
  StaffLog 
  LEFT JOIN 
    Items 
      ON 
      StaffLog.Name = Items.Name 
WHERE 
(  
  (
    DateCompleted >= #01/06/2016 00:00:00# and DateCompleted <= #01/06/2016 23:59:59#
  )
  and 
  (
    [DateCompleted] >= [LogIn] And [DateCompleted] <= [LogOut]
  )
)
GROUP BY 
  StaffLog.Name, 
  StaffLog.LogIn, 
  StaffLog.LogOut

I think those time additions might work
not sure though