Date query spanning 2 years

[jmar93]

HI,

I am using Access 2007 and have a query that returns values for the next week using the following code:

Year([shipdate])*53+DatePart("ww",[shipdate])=Year(Date())*53+DatePart("ww",Date())+1.

It has worked perfectly all year until now when next week starts in Dec 08 and concludes in Jan 09. It only returns the records with dates through 12/31 and does not show the records with dates of 1/2/09. Does anyone know how to fix this?

thanks,
Jeff

[NeoPa]

It looks like it only ever worked because most of it has never been tested. The handling of the last week in a year is more than clumsy. Can you specify in English exactly what you hope for it to provide?

[jmar93]

HI,

The query is written exactly according to Access Help's "examples of query criteria". I need it to return any records that fall in the week following the current week.

thanks,
Jeff

[NeoPa]

The query is written exactly according to Access Help's "examples of query criteria".

I'm sorry Jeff, but if that were true you wouldn't have code comparing a week number within a year, with a year value. I don't know what you think you have here but it's certainly not that.

As for what you need it to do, can you be a little more explicit. What you say you want is not even the same unit as this returns. You ask for a boolean (True / False) value, but this returns a number of weeks since a particular date.

I can only help if I have a clear understanding of exactly what is required.

[jmar93]

Below is copied & pasted from Access' own help file for query criteria.

Contain dates that fall during the following week:

Year([SalesDate])* 53+DatePart("ww", [SalesDate]) = Year(Date())* 53+DatePart("ww", Date()) + 1

Returns records of transactions that will take place next week. A week starts on Sunday and ends on Saturday.

The only thing I have changed is that I subsituted ShipDate for SalesDate.

I need the query to return any orders that have a ShipDate that falls within next week (12/28/08-1/3/09).

thanks,
Jeff

[NeoPa]

You're right Jeff (and I apologise). This does actually return a boolean value when interpreted correctly (which I wasn't doing). I placed the parentheses in the wrong places (in my head).

Having re-interpreted the code I find I can't see an obvious problem with it. Can you provide some examples (including the date it was run on to indicate the value of Date()) that you know fails, with the results you got (probably just a record not selected when you expected it to be or vice-versa).

[nico5038]

Weeks are a real pain.
It's depending on how the company registers weeks as there are several methods to determine week number 1.
In the year-end situation there's a "bonus" as for some years January 1 will return week 53 as it's part of 2008, but a:
?DatePart("ww", "1/1/2009", vbSunday, vbFirstFourDays )
will return:
53

So I created my own year/week function you can find in the attached database.
I also created a form that will fill a (temp) table with Year/Week combinations to get a range.
Just check the comment of the function and adapt it to your needs.

Nic;o)

[NeoPa]

Actually, I think I understand why it's not working. It is, after all, a bit of a kludge (Thank you Microsoft). It assumes that all years will consist of exactly 53 weeks (something we all know to be false). It won't cause problems in many instances, but I suspect the date you were working with was in the new year. Relative to the start of 2008 it is only 52 weeks on. If (as the code does) you assume 53 weeks per year you will automatically skip a week :( I would guess you may have had some false-positives for the succeeding week. After that all problems would go away for at least the rest of the year.

Anyway, try instead :

[ShipDate] Between Date()+8-Weekday(Date()) And Date()+14-Weekday(Date())

PS. This assumes the defaulted second parameter to Weekday() of vbSunday. This can be made explicit if you prefer.

[jmar93]

Hi guys,

After reading your responses I changed the 53 in the code to 52 and everything works perfectly at least for now, we'll see what happens a year from now. Thanks for your time and help.

Jeff

PS: Have a great Christmas

[NeoPa]

Did you try the code I suggested Jeff?

That should work perfectly in all circumstances (assuming your default start day of week is Sunday - otherwise simply add the vbSunday to make it explicit)

Merry Christmas anyway :)

[jmar93]

Hi NeoPa,

Your code works great so I will be switching over to it.

thanks

Jeff

[NeoPa]

Very pleased to hear it Jeff, and glad I could help :)