Week Numbers Query? (1 Viewer)

[robjones23]

Hi again,

Hope everyone had a good christmas / new year!

I need a little help with an access query. I'm trying to add a column called "Week Number" to a database with "logged Date / Time" as a record. I need the date / time field to be looked at and then a week number be produced from it depending on when it was logged.

For example,

This week, we're in week number 1 of the year. Week 1 runs from the 3rd January, to the 9th January (Mon - Sun) and then week 2 is the 10th Jan - 16th... etc....

I would like anything logged between the 3rd to the 9th to show "1" in the "Week Number" column I'm creating. If It helps, I do also have a column with "week" on it's own as dd/mm/yyyy (Rather than the "logged date / time" which is dd/mm/yyy hh:mm:ss).

Anyone know how to do this??

Thanks,

Rob.

 

[namliam]

You can simply use the format function, if you are in the NL or somewhere simular you will want to use:

Format([YourDate],"WW", 2,2)

Greetz

 

[robjones23]

lol thanks - I didn't think it'd be so simple

Much appreciated!

 

[rolfrobinson]

Week/year number query

You can simply use the format function, if you are in the NL or somewhere simular you will want to use:

Format([YourDate],"WW", 2,2)

Greetz

Hi

I have noticed that when you use week nos when the year ends the weeks go back to the beginning again throwing the results out.

This is the formula that i have put in my query
Week No: DatePart("ww",[Date Sent])

Is there anyway that you could put ww/yyyy? I have tried this but it shows an #error in the data field of the query. Would I have to put
Format([YourDate],"WW/YY", 2,2) for it to work?

thanks in advance

 

[robjones23]

This works and returns "53/04" for something which happened on 29/12/2004. I guess you could put "WW/YYYY" to return a full year so that there's no confusion whenn it comes to 04/04 04/03 etc....

 

[rolfrobinson]

date sort

thanks for that!!! it worked.

Do you know how I can arrange them on the query so that they are sorted in date order. What is happening is the dates are been sorted like this
:- 01/05, 50/04, 51/04, 52/04

The 01 is been put to the front of the crosstab query.

any suggestions, have tried ascending and descending.

 

[neileg]

Add the true date field to your query and sort on that.

 

[rolfrobinson]

Hi

I have tried this in a normal query and it works.
But in a crosstab query I am struggling.

 

[namliam]

Use the "In" part of a crosstab
Or
Use YY/WW

 

[jezjez]

This works and returns "53/04" for something which happened on 29/12/2004. I guess you could put "WW/YYYY" to return a full year so that there's no confusion whenn it comes to 04/04 04/03 etc....

Hi
This quote above is my query; why week 53? the last week of the year is 52, but my queries are returning week 53 followed by week 2...

any help appreciated, cheers Jez

 

[Pat Hartman]

A popular question this week. See this answer:
http://www.access-programmers.co.uk/forums/showthread.php?t=79002

 

[namliam]

The year 2004 in fact has 53 weeks (accoording to CET calander) some countries do it different

Lookup the format function in the access help and find out why i said to use the ,2,2 part.
They are actualy vb variables, which cannot be used in query's
"The latest news read all about about it !! (in the help )"

Regards

 

[EMP]

WeekNumber: Format([MyDate],"ww yyyy",2,2)


The first two days in 2005 returned 53 2005

MyDate		WeekNumber
25/12/2004	52 2004
26/12/2004	52 2004
27/12/2004	53 2004
28/12/2004	53 2004
29/12/2004	53 2004
30/12/2004	53 2004
31/12/2004	53 2004
[b]  1/1/2005	53 2005
  2/1/2005	53 2005[/b]
  3/1/2005	1 2005
  4/1/2005	1 2005
  5/1/2005	1 2005
  6/1/2005	1 2005
  7/1/2005	1 2005
  8/1/2005	1 2005
  9/1/2005	1 2005
 10/1/2005	2 2005
 11/1/2005	2 2005

 

[robjones23]

Not sure if you're asking why it does this here emp but I'll answer it as though it were a question

It's supposed to be like that

Week numbers go Monday - Sunday as one week. Week number 53 this year started monday December 27th and ended Sunday January 2nd. The first week of the new year begins Monday 3rd Januray and ends Sunday 9th.

This year there are 52 weeks, with the 52nd week beginning monday 26th of december aand ending on Sunday 1st January.

 

[jezjez]

hi
thanks for the answers guys, we're definately heading in the right direction.

I can live with the first couple of days of the year being week 53, but my query returns the next week (ie data for this week we're in now) as week 2...

very strange..

 

[namliam]

format(date()-weekday(date,2)+1,"YYYY WW",2,2)

Will take care of the 53 2005 and make it 53 2004

The other issue, are you sure your using ,2,2 ???? and not some other settings? Strange, i have with above statement no problems...

Regards

 

[lansel]

Week Number

I was having basically the same problem with my report except I need to pull the information by the last six weeks to arrive at a six weeks average. The data is entered daily and I needed the information by week, so I used Format([EntryDate],"ww/yy",2,2). When I request the beginning and ending week number, all weeks are listed and not just the ones I requested and week #1 2005 is listed first. I changed to yy/ww, but still pulled in all weeks of data.

Do you have a suggestion? This report worked fine until I had to include weeks from both 2004 and 2005.

Thanks,

 

[neileg]

Sometimes, using a criterion in a query based on a calculated field produces odd results. You need to evaluate the field first and then apply the citerion.

 

[A@Ron]

WeekNumber: Format([MyDate],"ww yyyy",2,2)


The first two days in 2005 returned 53 2005

MyDate        WeekNumber
25/12/2004    52 2004
26/12/2004    52 2004
27/12/2004    53 2004
28/12/2004    53 2004
29/12/2004    53 2004
30/12/2004    53 2004
31/12/2004    53 2004
[B]  1/1/2005    53 2005
  2/1/2005    53 2005[/B]
  3/1/2005    1 2005
  4/1/2005    1 2005
  5/1/2005    1 2005
  6/1/2005    1 2005
  7/1/2005    1 2005
  8/1/2005    1 2005
  9/1/2005    1 2005
 10/1/2005    2 2005
 11/1/2005    2 2005

I think I understand his point is the same that I have ran into this morning (welcome in 2016! and sorry for re-opening such an old thread). In his example, I would prefer 1/1 & 1/2 to be displayed as 53/2004.

I'm meeting the same problem and here is my example below. Here is my query:

Year/Week: Format([Prod Date],"yyyy/ww",2,2)

Prod Date: Year/Week
12/30/2015: 2015/53 (Great!)
12/31/2015: 2015/53 (Great!)
1/1/2016: 2016/53 (but I would prefer to see 2015/53)
1/2/2016: 2016/53 (but I would prefer to see 2015/53)
1/3/2016: 2016/53 (but I would prefer to see 2015/53)
1/4/2016: 2016/01 (Great!)
1/5/2016: 2016/01 (Great!)

Idears?

 

[JHB]

I had the same problem for many, many years ago, but luckily for me I came across the below function in the Internet (function written by George Simms). After using this function my entire weeknumber problem was gone, maybe it can help you too.

Function WeekNrAndYear(InputDate As Long) As String
  Dim a As Integer, B As Integer, C As Long, D As Integer
  WeekNrAndYear = 0
  If InputDate < 1 Then Exit Function
  a = Weekday(InputDate, 1)
  B = Year(InputDate + ((8 - a) Mod 7) - 3)
  Aar = B
  C = DateSerial(B, 1, 1)
  D = (Weekday(C, 1) + 1) Mod 7
  WeekNrAndYear = Format(CStr(Int((InputDate - C - 3 + D) / 7) + 1), "00") & "-" & CStr(Aar)
End Function