Week Numbers Query?

R

robjones23

Registered User.
Local time
Today, 22:03
Joined
Dec 16, 2004
Messages
66
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.
 
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
 
lol thanks - I didn't think it'd be so simple :rolleyes:

Much appreciated!
 
Week/year number query

namliam said:
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
Click to expand...


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
 
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....
 
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.
 
Add the true date field to your query and sort on that.
 
Hi

I have tried this in a normal query and it works.
But in a crosstab query I am struggling.
 
Use the "In" part of a crosstab
Or
Use YY/WW
 
robjones23 said:
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....
Click to expand...

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
 
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
 
WeekNumber: Format([MyDate],"ww yyyy",2,2)


The first two days in 2005 returned 53 2005
Code: 
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
 
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.
 
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..
 
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
 
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,
 
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.
 
EMP said:
WeekNumber: Format([MyDate],"ww yyyy",2,2)


The first two days in 2005 returned 53 2005
Code: 
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
Click to expand...

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?
 
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.
Code: 
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
 
You must log in or register to reply here.

Similar threads

essaytee
Is it possible: Query/SQL to dynamically display a record/order/position number, without underlying field from a table
Replies
11
Views
423
essaytee
essaytee
N
Create Output Query numbers in brackets
Replies
17
Views
411
DHookom
DHookom
D
Table Search and Total
Replies
18
Views
568
DDJKSA
D
W
Hello, New here
Replies
2
Views
201
DHookom
DHookom
C
Weekday Series
Replies
10
Views
532
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom