473,320 Members | 1,921 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Date query spanning 2 years

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
11 2489
NeoPa
32,556 Expert Mod 16PB
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
jmar93
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
32,556 Expert Mod 16PB
@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
jmar93
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
32,556 Expert Mod 16PB
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
3,080 Expert 2GB
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, 69 views)
Dec 22 '08 #7
NeoPa
32,556 Expert Mod 16PB
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
jmar93
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
32,556 Expert Mod 16PB
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
jmar93
46
Hi NeoPa,

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

thanks

Jeff
Dec 23 '08 #11
NeoPa
32,556 Expert Mod 16PB
Very pleased to hear it Jeff, and glad I could help :)
Dec 23 '08 #12

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

Similar topics

15
by: PMBragg | last post by:
Thank you in advance. I'm trying to pull all inventory items from December of the previous year back to 4 years for my accountant. I know this can be done, but I'm drawing a blank. I've tried; ...
7
by: Nicolae Fieraru | last post by:
Hi All, I have a table tblProducts where I have four fields:\ Index, ProductName, EnterDate (as Date/Time - Medium Date), PurchaseDate (Date/Time - Medium Date) The EnterDate is automatically...
5
by: tamilan71 | last post by:
Hello All I have table with following fields: GroupId VisitDate 1 10/19/1993 1 11/24/1998 2 10/18/1993 2 10/29/1998 3 ...
1
by: John Feeley | last post by:
am tring to add a number of years to a dob. im doing this by adding my date+years*365.26 I get a string of numbers. I then convert the number in the next column to actual date again. I'm getting...
2
by: Julie Wardlow | last post by:
Help! I am calculating a future date using the DateAdd function in a query (the calculation also involves an IIf statement), and have managed to get this formula to produce the required result....
7
by: Jerome | last post by:
Hallo, I know a lot has already been told about date/time fields in a database but still confuses me, specif when dealing with SQLserver(Express). It seems that sqlserver only accepts the date in...
3
by: Porkapalooza | last post by:
I've inherited a database that had a single flat table holding client and policy information. The policy information included individual fields for estimated commissions (i.e. "Comm 99"; "Comm...
4
by: MCLR | last post by:
Good day to all: I found your website a couple of days ago as I was searching for resources that can help within my particular problem in writing a script for an Access Query. The issue is to...
2
by: dympna | last post by:
Hi can anyone suggest a fix for this... as I am a novice in access. I have created a training table with the following fields Employee Name - joe Training Received - Fork lift Date Received...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.