Record missing using work week query (1 Viewer)

[Bat17]

found some time

SELECT TBL.MyDate
FROM TBL
WHERE (((IIf(Month([myDate])=1 And DatePart("WW",[myDate],1,2)>50,Year([myDate])-1,IIf(Month([myDate])=12 And DatePart("WW",[myDate],1,2)=1,Year([myDate])+1,Year([myDate]))))=2007));


Peter

 

[lsy]

found some time

SELECT TBL.MyDate
FROM TBL
WHERE (((IIf(Month([myDate])=1 And DatePart("WW",[myDate],1,2)>50,Year([myDate])-1,IIf(Month([myDate])=12 And DatePart("WW",[myDate],1,2)=1,Year([myDate])+1,Year([myDate]))))=2007));


Peter

Yeah, i got it... but could you pls do a brief explain on it??

 

[Bat17]

Month([myDate])=1 And DatePart("WW",[myDate],1,2)>50
if month is Jan and week is over 50 then count as last year - Year([myDate])-1
If m onth is Dec and week is one then count as next year - Year([myDate])+1

anything else then just use the year as normal - Year([myDate])

And in this case I have filtered all against 2007

Peter

 

[Bat17]

and ready for the next question, 'How do I filter past the year barrier?'

SELECT tbl.MyDate, DatePart("WW",[myDate],1,2) AS WeekNum, IIf(Month([myDate])=1 And DatePart("WW",[myDate],1,2)>50,Year([myDate])-1,IIf(Month([myDate])=12 And DatePart("WW",[myDate],1,2)=1,Year([myDate])+1,Year([myDate])))*100+DatePart("WW",[myDate],1,2) AS FilterYear
FROM tbl
WHERE (((IIf(Month([myDate])=1 And DatePart("WW",[myDate],1,2)>50,Year([myDate])-1,IIf(Month([myDate])=12 And DatePart("WW",[myDate],1,2)=1,Year([myDate])+1,Year([myDate])))*100+DatePart("WW",[myDate],1,2)) Between 200602 And 200708));

I have made a combind year/week number this will let you order the records if you use it in a grouped quey and have to drop the date. you can also use this field to for your criteria for filtering.
If you worked out how the original code worked then you will soon work out this variant

just drop the SQL in a query then look at it in design mode and you should be able to see ok how it works.

Peter

 

[lsy]

i'm confusing with all the bracket!!

 

[lsy]

found some time

SELECT TBL.MyDate
FROM TBL
WHERE (((IIf(Month([myDate])=1 And DatePart("WW",[myDate],1,2)>50,Year([myDate])-1,IIf(Month([myDate])=12 And DatePart("WW",[myDate],1,2)=1,Year([myDate])+1,Year([myDate]))))=2007));


Peter

I don't understand how 2007 is filter?? where is the equition of it?

 

[Bat17]

Its a three part iif statement that returns a year number using either:-
Year([myDate]) - 1, if the month is 1 but the week is >50 so a week 52 or 53 showing up in january 2008 would get year 2008-1 = 2007

Year([myDate]) + 1, if the month is 12 but the week is =1 so a week 1 showing up in December 2006 would get year 2006+1 = 2007

everything else is 2007 anyway.
Thats this blob

IIf(Month([myDate])=1 And DatePart("WW",[myDate],1,2)>50,Year([myDate])-1,
IIf(Month([myDate])=12 And DatePart("WW",[myDate],1,2)=1,Year([myDate])+1,
Year([myDate])))


Hope thats clearer

Peter