Problems with Date Add & summation of values

[PMK]

Object of the query outlined below is to sum cash values between saturday and
the following friday. Unfortunately the summation part of the query only
returns values from sunday to the following saturday therefore i am always
retrieving the wrong saturdays data.

Any help would be most appreciated.

PMK

SELECT Format(DatePart("ww",[Daterequired]),"00") & " " &
DateAdd("d",-Weekday([Daterequired])+0,[Daterequired]) & " and " &
DateAdd("d",-Weekday([Daterequired])+6,[Daterequired]) AS [Between],
Sum(Diary.Value) AS SumOfValue
FROM Diary
GROUP BY Format(DatePart("ww",[Daterequired]),"00") & " " &
DateAdd("d",-Weekday([Daterequired])+0,[Daterequired]) & " and " &
DateAdd("d",-Weekday([Daterequired])+6,[Daterequired]),
Format([DateRequired],"yyyy")
ORDER BY Format([DateRequired],"yyyy") DESC ,
Format(DatePart("ww",[Daterequired]),"00") & " " &
DateAdd("d",-Weekday([Daterequired])+0,[Daterequired]) & " and " &
DateAdd("d",-Weekday([Daterequired])+6,[Daterequired]) DESC;

 

[David Lloyd]

One thing you may want to look at is the second parameter for the WeekDay
function. You may want to specify that the week starts on Saturday, rather
than the default, which is Sunday. See Access help for more information on
this parameter.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Object of the query outlined below is to sum cash values between saturday
and
the following friday. Unfortunately the summation part of the query only
returns values from sunday to the following saturday therefore i am always
retrieving the wrong saturdays data.

Any help would be most appreciated.

PMK

SELECT Format(DatePart("ww",[Daterequired]),"00") & " " &
DateAdd("d",-Weekday([Daterequired])+0,[Daterequired]) & " and " &
DateAdd("d",-Weekday([Daterequired])+6,[Daterequired]) AS [Between],
Sum(Diary.Value) AS SumOfValue
FROM Diary
GROUP BY Format(DatePart("ww",[Daterequired]),"00") & " " &
DateAdd("d",-Weekday([Daterequired])+0,[Daterequired]) & " and " &
DateAdd("d",-Weekday([Daterequired])+6,[Daterequired]),
Format([DateRequired],"yyyy")
ORDER BY Format([DateRequired],"yyyy") DESC ,
Format(DatePart("ww",[Daterequired]),"00") & " " &
DateAdd("d",-Weekday([Daterequired])+0,[Daterequired]) & " and " &
DateAdd("d",-Weekday([Daterequired])+6,[Daterequired]) DESC;

 

[PMK]

I have altered the parameters severa ltimes and still am unable to get
saturday as the commencing day. Access help is particularly unhelpful.

Any further specifics?

Thanks in advance

PMK

 

[David Lloyd]

Below is a query that I believe will give you the results you desire. I
have made two adjustments. One to add the firstdayofweek parameter to both
the DatePart and DateAdd function. Two, I have adjusted the offsets from 0
and 6 to 1 and 7.

SELECT Format(DatePart("ww",[Daterequired],7),"00") & " " &
DateAdd("d",-Weekday([Daterequired],7)+1,[Daterequired]) & " and " &
DateAdd("d",-Weekday([Daterequired],7)+7,[Daterequired]) AS [Between],
Sum(Diary.Value) AS SumOfValue
FROM Diary
GROUP BY Format(DatePart("ww",[Daterequired],7),"00") & " " &
DateAdd("d",-Weekday([Daterequired],7)+1,[Daterequired]) & " and " &
DateAdd("d",-Weekday([Daterequired],7)+7,[Daterequired]),
Format([DateRequired],"yyyy")
ORDER BY Format([DateRequired],"yyyy") DESC ,
Format(DatePart("ww",[Daterequired],7),"00") & " " &
DateAdd("d",-Weekday([Daterequired],7)+1,[Daterequired]) & " and " &
DateAdd("d",-Weekday([Daterequired],7)+7,[Daterequired]) DESC;


--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have altered the parameters severa ltimes and still am unable to get
saturday as the commencing day. Access help is particularly unhelpful.

Any further specifics?

Thanks in advance

PMK

One thing you may want to look at is the second parameter for the WeekDay
function. You may want to specify that the week starts on Saturday, rather
than the default, which is Sunday. See Access help for more information on
this parameter.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Object of the query outlined below is to sum cash values between saturday
and
the following friday. Unfortunately the summation part of the query only
returns values from sunday to the following saturday therefore i am always
retrieving the wrong saturdays data.

Any help would be most appreciated.

PMK

SELECT Format(DatePart("ww",[Daterequired]),"00") & " " &
DateAdd("d",-Weekday([Daterequired])+0,[Daterequired]) & " and " &
DateAdd("d",-Weekday([Daterequired])+6,[Daterequired]) AS [Between],
Sum(Diary.Value) AS SumOfValue
FROM Diary
GROUP BY Format(DatePart("ww",[Daterequired]),"00") & " " &
DateAdd("d",-Weekday([Daterequired])+0,[Daterequired]) & " and " &
DateAdd("d",-Weekday([Daterequired])+6,[Daterequired]),
Format([DateRequired],"yyyy")
ORDER BY Format([DateRequired],"yyyy") DESC ,
Format(DatePart("ww",[Daterequired]),"00") & " " &
DateAdd("d",-Weekday([Daterequired])+0,[Daterequired]) & " and " &
DateAdd("d",-Weekday([Daterequired])+6,[Daterequired]) DESC;