Can you query by week number rather than between dates

[ptaylor-west]

Is it possible to query using the week number in the year as criteria. For example instead of putting 13/05/05 to 20/05/05 can we use week 12 - the idea is to save on keying info in each time.

 

[Bat17]

Create a new column in you query using
WeekNum: DatePart('ww',[YourDateField])
This will then give you a column with week numbers that you can filter against.
Look up the Datepart function in help to find details on setting the first day of week and first week of year to suit you local needs.

HTH

Peter

 

[ptaylor-west]

Thanks, greatly appreciated

 

[Pat Hartman]

If you have data for multiple years in your table, you'll want to include year also. You can default to the current year if that makes sense or have the user enter a year.

 

[ptaylor-west]

Is it possible to use the computers date time as the default year - the limitations with this is that it would also need to be set so that if the week selected is not possible for the current year ie selecting week 52 in Febuary - then it would choose the previous year in the calculation?

 

[Bat17]

in your date field use criteria to limit it to records created in the last year
>Date()-365

Peter