Weektodate

G

gm6243

Dear Friends,

I have an Access database for sales over an year. I need to break this down
on a weekly basis. My year begins in November and ends in October. My week
begins on Friday and ends on Thursday.

My table has the following fields"

ServDate, CustName, Saleval

I want to create a Crosstab query in the following manner:

Weekending
Customer Name 05/01/08 05/08/08 05/15/08 .......

ABC 5000 7000 2000
CDE 1000 2500 1800

and so on

Can someone please help me with this.

Thank you


Gmen
 
G

Graham Mandeno

Hi Gmen

The following function will return the last day of the week containing the
given date:

Public Function EndOfWeek(GivenDate As Variant, _
Optional FirstDayOfWeek As VbDayOfWeek = vbUseSystemDayOfWeek _
) As Variant
If IsDate(GivenDate) Then
EndOfWeek = DateValue(GivenDate) - Weekday(GivenDate, FirstDayOfWeek) + 7
End If
End Function

By default it uses the first day of the week from your system settings, but
you can specify a different first day.

For example, EndOfWeek(Date, vbFriday) gives 19 Jun 2008.

If you paste this function into a standard module, you can use it in your
query, but SQL will not understand vbFriday, so use 6 instead.

So, set your Row Heading field to:
Week: EndOfWeek( [ServDate], 6 )
 
Top