Between WHERE clause

  • Thread starter Thread starter red6000
  • Start date Start date
R

red6000

Hi, I have the following SQL

SELECT Activities.TaskName, DSum("TimeSpent","Activities","TaskName = '" &
[TaskName] & "'") AS TotalTime
FROM Activities
WHERE (((Activities.WorkDate) Between [Start Period] And [End Period]))
GROUP BY Activities.TaskName
ORDER BY Val(DSum("TimeSpent","Activities","TaskName = '" & [TaskName] &
"'")) DESC;

However it isn't filtering out the records where the date is outside what I
specify?

Any ideas

Many thanks.
 
Okay, I've simplified my SQL to:

SELECT Activities.TaskName, Sum(Activities.TimeSpent) AS TotalTime
FROM Activities
WHERE (((Activities.WorkDate)="30/07/2006"))
GROUP BY Activities.TaskName;

and this works, but if I change the Where clause to:

WHERE (((Activities.WorkDate) between "30/07/2006" and "01/08/2006"))

It just includes everything?????
 
red6000 said:
Okay, I've simplified my SQL to:

SELECT Activities.TaskName, Sum(Activities.TimeSpent) AS TotalTime
FROM Activities
WHERE (((Activities.WorkDate)="30/07/2006"))
GROUP BY Activities.TaskName;

and this works, but if I change the Where clause to:

WHERE (((Activities.WorkDate) between "30/07/2006" and "01/08/2006"))

It just includes everything?????
Click to expand...

Dates in Access are delimited with # not ". They also have to be in US format
mm/dd/yy or mm/dd/yyyy, or in ISO format yyyy-mm-dd or in a format that uses
alpha characters for the month like dd-mmm-yyyy.

The format you are using dd/mm/yyyy will only be interpretted correctly when the
month is greater than 12. Then Access will "know" that the first part must be
the day. Otherwise it will assume the first part is the month.
 
Back
Top