DatePart will only accept 6th Jan 2005 as week 2

[ptaylor-west]

When using 'ww' as the criteria in the DatePart calculation it will not accept 1 for 6th Jan 2005 which is week 1, you have to enter 2 and it will select it, in fact all the weeks so far in 2005 are out by 1. To cure it you can add on -1 to the query and it works fine until you go back to the previous year and it screws up those dates.

Any ideas?

 

[Mile-O]

Have you set the DatePart() function's optional arguments for the first week of the year?

 

[ptaylor-west]

No, I didn't know you could set any - presumably you have to set them each year then?

I simply used 'ww' and the Mydate field with the criteria [Enter week number] and then another with 'yyyy' and the Mydate field with the criteria [Enter year]

I have searched to find more information about DatePart and the options you mention but it appears to be one of the best kept secrets, I would apprecite your guidance.

 

[Mile-O]

I have searched to find more information about DatePart and the options you mention but it appears to be one of the best kept secrets

Best kept secrets? It's explained fully in the help files!

Personally, though, I don't agree with using paramters in queries such as you are using. I prefer to refer to textboxes on forms.

 

[ptaylor-west]

I can find nothing in the help files that explains how to set the first week of the year. The query is also for selecting records to output to a report - not a form.

Thanks anyway, I will look elsewhere.

 

[neileg]

From the Access help files:

DatePart Function

Returns a Variant (Integer) containing the specified part of a given date.

Syntax

DatePart(interval, date[,firstdayofweek[, firstweekofyear]])

The DatePart function syntax has these named arguments:

Part Description
interval Required. String expression that is the interval of time you want to return.
date Required. Variant (Date) value that you want to evaluate.
firstdayofweek Optional. A constant that specifies the first day of the week. If not specified, Sunday is assumed.
firstweekofyear Optional. A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.

 

[ptaylor-west]

I did see this but no explanation or examples - if I want to make the 1st January as week 1 every year what do I need to put in as firstweekofyear, is it simply 1 or do I put =1 after the expression

 

[neileg]

The firstweekofyear argument has these settings:

Constant         Value Description 
vbUseSystem    0      Use the NLS API setting. 
vbFirstJan1       1      Start with week in which January 1 occurs (default). 
vbFirstFourDays 2      Start with the first week that has at least four days in the new year. 
vbFirstFullWeek  3      Start with first full week of the year.

Do you have a problem with your help files? The full help is available if you open a VBA code window and then open Help.

 

[ptaylor-west]

Aha! that's why I can't see it normally - I thought I was going mad.

Thanks for your help.

 

[ptaylor-west]

I'm still stuck - I can't logically see how I do this, my query criteria is as follows:

Weeknum: DatePart('ww',[Visitdate])

What do I need to add (exactly please) so that it defaults to option 2 as the first week of the year (this is the one where it needs to have at least 4 days for the first week)

 

[Rich]

Weeknum: (DatePart('ww',[Visitdate])= 2) ??

 

[ptaylor-west]

Thanks for that, there must be differences on which version you use - the final solution I found was as follows:

Weeknum: DatePart('ww',[Visitdate],2,2)

The first 2 makes the start of the week default day Monday, the second 2 makes the first week of the year the one that has at least 4 days. You must include both options for this result, or leave the first day of the week blank to use the default as below:

Weeknum: DatePart('ww',[Visitdate],,2)

The following example (ommiting the second option) simply sets the first day of the week to Monday:

Weeknum: DatePart('ww',[Visitdate],2)

Many thanks for everyones help, as usual it was greatly appreciated