Query By Week Properties

[sacsadmin]

Is there a way to set the start week in a query that is filtered by week? Example: Our fiscal year starts on February 01, not on January 01. So I need week 1 to be started on February 01.

Also, is there a way to control the formatting. On my report is shows the week output as a number of the week (1,2,3 etc.) I would want it to show the date period of that week (example 02/03/03 to 02/09/03.

As always, thanks in advance for your help.

 

[PaulBricker]

You might be able to write a custom Function to handle the week number situation. On issue is that the first week doesn't often start on Jan 1. It start on the Sunday that contains the week Jan 1 is in so Dec 31 and Jan 1 are often in the same week. Do you want you week to start on Feb 1?

As for the week number vs. date range, in your query that supplies the Report, you can put something like this.

MyWeek:[DateField] - Weekday([DateField]) + 1 & " to " & [DateField] - Weekday([DateField]) + 7

That will return Sunday to Saturday dates for any value contained in the datefield. Then in your report reference the field MyWeek to get what you want.

Paul

 

[PaulBricker]

Well here's the disclaimer. I'm not 100% positive this won't error out so double check the values but at least it did work for my testing dates (but if anyone would like to kick this around I'd appreciate it). My main concern is around the end of January the following year.
Put this function in a module.

Function FiscalWeek(myDte As Date) As Integer
Dim myYear As Integer
If Month(myDte) >= 2 Then
myYear = Year(myDte)
Else
myYear = Year(myDte) - 1
End If
FiscalWeek = Int(([myDte] - (DateSerial(myYear, 2, 1) - Weekday(DateSerial(myYear, 2, 1)) + 1)) / 7) + 1

End Function

Then in your query call it using

MyWeek:FiscalWeek([DateField])
I will look it over some more but thought you could try it out in a query and see how your results worked.

Paul

 

[PaulBricker]

I have to amend what I said about when the week count starts. It appears, after running a bunch of dates thru the DatePart() function that Week1 starts as of Jan 1 and that Dec 31 (the day before), is part of the 53 week of the year. That being true, then the function should work as advertised. Again, if anyone has comments, please feel free to express them.

Paul