Record missing using work week query

[lsy]

I got a problem regarding query work week in database.
The database contain data of year 2006 and 2007. When i query about work week, some record is missing.

i wrote the sql statement as
SELECT * FROM TBL WHERE FORMAT(MYDATE, 'WW', 1, 2) = 1 AND YEAR(2007);

There is one record missing... which is 31/12/2006 record.

So any idea to eliminate this??

 

[boblarson]

Yeah,

Change AND YEAR(2007);

to AND YEAR(2006) OR YEAR(2007);

Or if it is all just 2006 and 2007, just get rid of the AND YEAR(2007) completely.

 

[lsy]

Yeah,

Change AND YEAR(2007);

to AND YEAR(2006) OR YEAR(2007);

Or if it is all just 2006 and 2007, just get rid of the AND YEAR(2007) completely.

if i change to "AND YEAR(2006) OR YEAR(2007)" it will mixed up 2006 and 2007 work week 1 record.

 

[boblarson]

Just include an ORDER BY MyDate in the query.

 

[lsy]

Just include an ORDER BY MyDate in the query.

ya the query will include 2006 and 2007 record but i just want 2007 work week 1 record.

 

[boblarson]

I got a problem regarding query work week in database.
The database contain data of year 2006 and 2007. When i query about work week, some record is missing.

i wrote the sql statement as
SELECT * FROM TBL WHERE FORMAT(MYDATE, 'WW', 1, 2) = 1 AND YEAR(2007);

There is one record missing... which is 31/12/2006 record.

So any idea to eliminate this??

Okay, let's try to reason this out. I don't think you are really stating what you really want. First of all you said that you have a missing record (31/12/2006) and using a query to pull YEAR(2007) will OBVIOUSLY NOT PULL anything for 2006 - (31/12/2006 is 2006, the last time I checked). So, you said nothing originally about having two week ones, one for 2006 and one for 2007.

So, I really don't think I can help you if you aren't able to specify EXACTLY what you want (and not have the criteria conflict with one another).

 

[lsy]

Okay, let's try to reason this out. I don't think you are really stating what you really want. First of all you said that you have a missing record (31/12/2006) and using a query to pull YEAR(2007) will OBVIOUSLY NOT PULL anything for 2006 - (31/12/2006 is 2006, the last time I checked). So, you said nothing originally about having two week ones, one for 2006 and one for 2007.

So, I really don't think I can help you if you aren't able to specify EXACTLY what you want (and not have the criteria conflict with one another).

i have make some changes on my query. my new query will be
SELECT * FROM TBL WHERE FORMAT(MYDATE, 'WW yyyy', 1, 2) = '1 2007'
so 31/12/2006 is fall under work week 1 in year 2007 but this record is not query out too in this statement...
what i want is record from 31/12/2006 till 6/1/2007 which query by work week.

 

[Bat17]

rather than filter by year could you not filter by date? >1/1/2006

Peter

 

[lsy]

rather than filter by year could you not filter by date? >1/1/2006

Peter

i'm filtering by workweek

 

[Bat17]

yes, but add a date criteria as second criteria rather than a year criteria, this will give you the flexabilty you need to cross year boundries

Peter

 

[lsy]

yes, but add a date criteria as second criteria rather than a year criteria, this will give you the flexabilty you need to cross year boundries

Peter

how can i use the date criteria as second criteria?? the criteria must be work week and it return the date based on the work week criteria.

 

[Bat17]

something like:-
SELECT MyDate FROM TBL WHERE MyDate>#12/1/2006# AND DatePart("ww",MyDate,1,2)=1;

Peter

 

[Bat17]

Also if you are working to ISO 8601 you might want to check out this article
http://support.microsoft.com/kb/q200299/

Peter

 

[lsy]

Thanks a lots... that is easy!! why i never think of it??

 

[lsy]

i got a new question... what if now i would like to query a range of work week??
eg: workweek 1 to workweek 6 in year 2007

 

[Bat17]

SELECT TBL.MyDate
FROM TBL
WHERE (((TBL.MyDate)>#12/1/2006#) AND ((DatePart("ww",[MyDate],1,2)) Between 1 And 6));

Though if I were doing this I would use a form to supply the parameters so that you can have a start week/year and end week/year combination. Then use hidden fields to adjust the dates from the years supplied.

HTH

Peter

 

[lsy]

SELECT TBL.MyDate
FROM TBL
WHERE (((TBL.MyDate)>#12/1/2006#) AND ((DatePart("ww",[MyDate],1,2)) Between 1 And 6));

Though if I were doing this I would use a form to supply the parameters so that you can have a start week/year and end week/year combination. Then use hidden fields to adjust the dates from the years supplied.

HTH

Peter

if i follow this way how can query for 1-52 in 2007?? that might have duplicate data for ww52

 

[Bat17]

Nice question
My factory shuts between christmas and new year so I had not noticed this before.
I think that the only way around it will to be with a custom function.
Put this code in a module and save the module (with a differnt name than the function or Access will spit its dummy out!)

Function WeekYear(InDate As Date) As Integer
Dim intWeek As Integer
   intWeek = DatePart("WW", InDate, 1, 2)
  If Month(InDate) = 1 And intWeek > 50 Then
     WeekYear = Year(InDate) - 1
ElseIf Month(InDate) = 12 And intWeek = 1 Then
        WeekYear = Year(InDate) + 1
Else
    WeekYear = Year(InDate)
End If
End Function

Then your SQL could be

SELECT TBL.MyDate
FROM TBL
WHERE (((DatePart("ww",[MyDate],1,2)) Between 1 And 52) AND ((weekyear([MyDate]))=2007));

HTH

Peter

 

[lsy]

Nice question
My factory shuts between christmas and new year so I had not noticed this before.
I think that the only way around it will to be with a custom function.
Put this code in a module and save the module (with a differnt name than the function or Access will spit its dummy out!)

Function WeekYear(InDate As Date) As Integer
Dim intWeek As Integer
   intWeek = DatePart("WW", InDate, 1, 2)
  If Month(InDate) = 1 And intWeek > 50 Then
     WeekYear = Year(InDate) - 1
ElseIf Month(InDate) = 12 And intWeek = 1 Then
        WeekYear = Year(InDate) + 1
Else
    WeekYear = Year(InDate)
End If
End Function

Then your SQL could be

SELECT TBL.MyDate
FROM TBL
WHERE (((DatePart("ww",[MyDate],1,2)) Between 1 And 52) AND ((weekyear([MyDate]))=2007));

HTH

Peter

I'm writing a program to pass the sql to query the database so that i unable to write a module on it. Do you still have any other way to do this? i'm sorry to bordering yous o much...

 

[Bat17]

Don't have time to look at it at the moment but it is probably possible to take the code and create an IIF() statement bassed on it as all the functions in it are standard ones.

peter