Week starts on Friday

[CJ]

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.

 

[KARL DEWEY]

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")

 

[CJ]

Perfect!

Thanks Karl!
--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!

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.

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.

 

[Marshall Barton]

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.