Week No in a query

[Djbell]

Hi All

I have a query that has a date field, I would like to add another Field called Week No. Week number would then automatically assign a week number to the date..

So for example.

If Week No 1 starts on 02/01/2006

Then the following dates should be labelled with the following Week No's

13/03/2006 Week 11
04/04/2006 Week 14
15/06/2006 Week 24

Any help is appreciated.

Regards

Douglas Bell

 

[Sumitdev]

You could try the DateDiff function:

SELECT  X.Date, 'Week ' & ( DateDiff('ww',  datevalue('02/01/06'), X.Date) + 1) AS WeekNo 
FROM X

 

[PHV]

Have you tried this ?
DatePart("ww", [date field])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886