Problems with Date Add & summation of values

G

Guest

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;
 
D

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;
 
G

Guest

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
 
D

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

David Lloyd said:
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;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top