By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,389 Members | 2,052 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,389 IT Pros & Developers. It's quick & easy.

Date query spanning 2 years

P: 46
HI,

I am using Access 2007 and have a query that returns values for the next week using the following code:
Expand|Select|Wrap|Line Numbers
  1. 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
Dec 22 '08 #1
Share this Question
Share on Google+
11 Replies


NeoPa
Expert Mod 15k+
P: 31,494
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?
Dec 22 '08 #2

P: 46
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
Dec 22 '08 #3

NeoPa
Expert Mod 15k+
P: 31,494
@jmar93
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.
Dec 22 '08 #4

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

Contain dates that fall during the following week:
Expand|Select|Wrap|Line Numbers
  1. 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
Dec 22 '08 #5

NeoPa
Expert Mod 15k+
P: 31,494
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).
Dec 22 '08 #6

nico5038
Expert 2.5K+
P: 3,072
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)
Attached Files
File Type: zip Week.zip (17.8 KB, 51 views)
Dec 22 '08 #7

NeoPa
Expert Mod 15k+
P: 31,494
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 :
Expand|Select|Wrap|Line Numbers
  1. [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.
Dec 22 '08 #8

P: 46
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
Dec 23 '08 #9

NeoPa
Expert Mod 15k+
P: 31,494
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 :)
Dec 23 '08 #10

P: 46
Hi NeoPa,

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

thanks

Jeff
Dec 23 '08 #11

NeoPa
Expert Mod 15k+
P: 31,494
Very pleased to hear it Jeff, and glad I could help :)
Dec 23 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.