Query Week and Year

[Tanya]

Hi I have the following query which tallies the daily points and need to be able to select the week from mon-fri and for the year to date.

I have spent hours on this without success. If anyone could help I would appreciate it.

::::Selects Daily tally::::::::

SELECT tblBehaviour.BehaviourID, tblBehaviour.StudentID, tblBehaviour.BehaviourDate, tblBehaviour.Session, [Listening/Takes Turn]+[Follows Instructions]+[Accept Feedback Behaviour]+[Accept Feedback Academic]+[Respects Rules]+[Requests/Negotiates]+[Polite Language]+[Completes IEP Task]+[Participates in Class Meeting]+[Completes Homework]+[Uses Self Control Strategies]+[Prepares/Packs Materials]+[Bonus]-[Penalty]-[Lunch] AS [Session Tally]
FROM tblBehaviour
GROUP BY tblBehaviour.BehaviourID, tblBehaviour.StudentID, tblBehaviour.BehaviourDate, tblBehaviour.Session, [Listening/Takes Turn]+[Follows Instructions]+[Accept Feedback Behaviour]+[Accept Feedback Academic]+[Respects Rules]+[Requests/Negotiates]+[Polite Language]+[Completes IEP Task]+[Participates in Class Meeting]+[Completes Homework]+[Uses Self Control Strategies]+[Prepares/Packs Materials]+[Bonus]-[Penalty]-[Lunch]
HAVING (((tblBehaviour.BehaviourDate)=Date()));

I also managed to find an expression for weekstart but can't get any further - I thought that I could use this expression and add 5 days but it doesn't work:::::

WeekStart: IIf(Weekday([BehaviourDate])=1,DateSerial(Year([BehaviourDate]),Month([BehaviourDate]),Day([BehaviourDate])-6),IIf(Weekday([BehaviourDate])=2,[BehaviourDate],IIf(Day([BehaviourDate])>7,DateSerial(Year([BehaviourDate]),Month([BehaviourDate]),Day([BehaviourDate]-(Weekday([BehaviourDate])-2))),DateSerial(Year([BehaviourDate]),Month([BehaviourDate]-1),Day([BehaviourDate]-(Weekday([BehaviourDate])-2))))))


Cheers
Tanya

 

[DCrake]

If you add two more columns

Wk:Format(BehaviourDate,"ww")
Yr:Format(BevaviourDate,"yyyy")

Will give you the week number and year part of the date

 

[Tanya]

Excellent, Thankyou very much. You have saved me a lot of time.

regards
Tanya

 

[naungsai]

You can also do it with a single column,
Wks: format([BehaviourDate],"YYYY WW")

 

[Tanya]

Thank you.