Hi,
SELECT DateAdd("d",-Weekday([date])+2,[date]) AS BeginDate,
DateAdd("d",-Weekday([date])+6,[date]) AS EndDate,
qryTelephoneCalls.Date,
Sum(qryTelephoneCalls.CityNewApps) AS SumOfCityNewApps
FROM qryTelephoneCalls
WHERE DateAdd("d",-Weekday([date])+6,[date])
= DateAdd("d",-Weekday([End Date of the week])+6,[End Date of the week])
GROUP BY DateAdd("d",-Weekday([date])+2,[date]),
DateAdd("d",-Weekday([date])+6,[date]), qryTelephoneCalls.Date
ORDER BY qryTelephoneCalls.Date DESC;
should do. I just added the WHERE clause. The SUM will also implies records
with a date of the Saturday or the Sunday of the SAME WEEK, if there is any
such record. Instead of adding 6-Weekday( ... ), we could have use
DataPart("ww", .... ) in the WHERE clause:
WHERE DatePart("ww", [date] ) = DatePart("ww", [End Date of the week] )
as long as there is data for ONLY ONE YEAR in the table. If not, the first
formulation is to be used.
Hoping it may help,
Vanderghast, Access MVP
iholder said:
Sorry, I deleted the orginally query by accident.
This a another test query of what I am try to achieve.
SELECT DateAdd("d",-Weekday([date])+2,[date]) AS BeginDate,
DateAdd("d",-Weekday([date])+6,[date]) AS EndDate, qryTelephoneCalls.Date,
Sum(qryTelephoneCalls.CityNewApps) AS SumOfCityNewApps
FROM qryTelephoneCalls
GROUP BY DateAdd("d",-Weekday([date])+2,[date]),
DateAdd("d",-Weekday([date])+6,[date]), qryTelephoneCalls.Date
ORDER BY qryTelephoneCalls.Date DESC;
I need the user to enter only the End Date of the week. And retrieve all
the
records summed from Monday to Friday of that week.
Michel Walsh said:
Hi,
Can you post the SQL statement? (if it is different than the first one
you
post, which does not have any occurrence of
"qry.WeekEnding.[Enddate]=[Enter]" )
Hoping it may help,
Vanderghast, Access MVP
My query does not including the HAVING clause. i am using the WHERE
clause
:
Hi,
Seems you enter the criteria under the HAVING clause; try under the
WHERE
clause. If you use the grid, you have to change the total line from
GROUP
BY
to WHERE ... but sure, if you need the GROUP BY, add an extra column,
so,
in
the end, may be easier to do it in the SQL view.
The WHERE clause is evaluated before any aggregation, the HAVING
clause
is
evaluated after. So, whatever appear in the HAVING must either already
been
aggregated, or be part of the GROUPs
Hoping it may help,
Vanderghast, Access MVP
Please note. This the error message that I am getting when I run the
query
with the parameter input.
"You tried to execute a query that does not include the specified
expressionqry.WeekEnding.[Enddate]=[Enter] as part of an aggregate
function."
:
That is not working. I need user to input the end date. If I hard
code
it.
It work. I trying using [Enter End Date] for the criteria on the
DateAdd("d",-Weekday([date])+6,[date]) column of the query. But
the
query
comes up blank.
:
Hi,
either
WHERE DatePart( "ww", [date] ) = DatePart( "ww",
parameterDate )
either
WHERE [date] BETWEEN parameterDate-6 AND parameterDate
If there is a time, in addition to the date, in the [date] field,
try
instead:
WHERE [date] >= parameterDate-6 AND [date] < parameterDate +1
Hoping it may help,
Vanderghast, Access MVP
The query below formats the start and end of a work work. But I
am
unable
to
set the criteria for a specific end date.
SELECT tblTelephoneCalls.CityNewApps,
DateAdd("d",-Weekday([date])+2,[date])
AS startdate, DateAdd("d",-Weekday([date])+6,[date]) AS enddate
FROM tblTelephoneCalls
GROUP BY tblTelephoneCalls.CityNewApps,
tblTelephoneCalls.CityNewApps,
DateAdd("d",-Weekday([date])+2,[date]),
DateAdd("d",-Weekday([date])+6,[date])
ORDER BY DateAdd("d",-Weekday([date])+2,[date]) DESC;
I would like the user to be able to enter the week ending date.
The
criteria does not work when I enter the week ending date. The
query
come
up
blank.
It there a way to filter the week ending date without using the
week
number
format .
