Week starts on Friday
in [Queries]
|
From: CJ on 19 May 2010 19:34 Hi Groupies I have created a calculated field that is supposed to specify the week a job occurred. The week starts on Friday and goes through the following Thursday. My calculated field looks like this: WorkPeriod: Format(DateAdd("d",-1*(DatePart("w",[Ticket Date])+1),[Ticket Date]),"mmm dd""/""yy") & " - " & Format((DateAdd("d",-1*(DatePart("w",[Ticket Date])+1),[Ticket Date])+6),"mmm dd""/""yy") It returns a period formatted as: Dec 04/09 - Dec 10/09 (for example) However, it is not putting the Friday's and Saturday's into the correct week. For example November 6 and 7 2009 are showing up in the week of Oct 30 - Nov 05 and November 13 and 14 are showing up in the week of Nov 06 - Nov 12. Can somebody please help me straighten this out. -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups!
From: KARL DEWEY on 19 May 2010 21:50 Try this -- Format(DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket Date]),"mmm dd/yy") & " - " & Format(DateAdd("d",-Weekday([Ticket Date],6)+7,[Ticket Date]),"mmm dd/yy") -- Build a little, test a little. "CJ" wrote: > Hi Groupies > > I have created a calculated field that is supposed to specify the week a job > occurred. The week starts on Friday and goes through the following Thursday. > My calculated field looks like this: > > WorkPeriod: Format(DateAdd("d",-1*(DatePart("w",[Ticket Date])+1),[Ticket > Date]),"mmm dd""/""yy") & " - " & > Format((DateAdd("d",-1*(DatePart("w",[Ticket Date])+1),[Ticket > Date])+6),"mmm dd""/""yy") > > It returns a period formatted as: Dec 04/09 - Dec 10/09 (for example) > > However, it is not putting the Friday's and Saturday's into the correct > week. For example November 6 and 7 2009 are showing up in the week of Oct > 30 - Nov 05 and November 13 and 14 are showing up in the week of Nov 06 - > Nov 12. > > Can somebody please help me straighten this out. > -- > Thanks for taking the time! > > CJ > --------------------------------------------------------- > Know thyself, know thy limits....know thy newsgroups! > > . >
From: CJ on 19 May 2010 22:38 Perfect! Thanks Karl! -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! "KARL DEWEY" <KARLDEWEY(a)discussions.microsoft.com> wrote in message news:D3E4796D-831A-4DCB-B7A5-94B503E92622(a)microsoft.com... > Try this -- > Format(DateAdd("d",-Weekday([Ticket Date],6)+1,[Ticket Date]),"mmm dd/yy") > & > " - " & Format(DateAdd("d",-Weekday([Ticket Date],6)+7,[Ticket Date]),"mmm > dd/yy") > -- > Build a little, test a little. > > > "CJ" wrote: > >> Hi Groupies >> >> I have created a calculated field that is supposed to specify the week a >> job >> occurred. The week starts on Friday and goes through the following >> Thursday. >> My calculated field looks like this: >> >> WorkPeriod: Format(DateAdd("d",-1*(DatePart("w",[Ticket Date])+1),[Ticket >> Date]),"mmm dd""/""yy") & " - " & >> Format((DateAdd("d",-1*(DatePart("w",[Ticket Date])+1),[Ticket >> Date])+6),"mmm dd""/""yy") >> >> It returns a period formatted as: Dec 04/09 - Dec 10/09 (for example) >> >> However, it is not putting the Friday's and Saturday's into the correct >> week. For example November 6 and 7 2009 are showing up in the week of Oct >> 30 - Nov 05 and November 13 and 14 are showing up in the week of Nov 06 - >> Nov 12. >> >> Can somebody please help me straighten this out. >> -- >> Thanks for taking the time! >> >> CJ >> --------------------------------------------------------- >> Know thyself, know thy limits....know thy newsgroups! >> >> . >>
From: Marshall Barton on 19 May 2010 22:48 CJ wrote: >I have created a calculated field that is supposed to specify the week a job >occurred. The week starts on Friday and goes through the following Thursday. >My calculated field looks like this: > >WorkPeriod: Format(DateAdd("d",-1*(DatePart("w",[Ticket Date])+1),[Ticket >Date]),"mmm dd""/""yy") & " - " & >Format((DateAdd("d",-1*(DatePart("w",[Ticket Date])+1),[Ticket >Date])+6),"mmm dd""/""yy") > >It returns a period formatted as: Dec 04/09 - Dec 10/09 (for example) > >However, it is not putting the Friday's and Saturday's into the correct >week. For example November 6 and 7 2009 are showing up in the week of Oct >30 - Nov 05 and November 13 and 14 are showing up in the week of Nov 06 - >Nov 12. The DatePart function has a third argument that you can use to specify the first day of the week. It is unusual to use the Format function in a query. Generally, it is better to leave the field as a date/time value instead ov converting it to text. Format the value using the format property of a form or report text box. Note that may be slightly easier to use the Weekday function omstead of DatePart. -- Marsh MVP [MS Access]
|
Pages: 1 Prev: Access 2003: Select Next Item in Combobox Next: count qry |