UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Week Number, Access 2016    
 
   
lilus615
post Apr 6 2020, 11:24 PM
Post#1



Posts: 116
Joined: 6-September 06
From: CA, USA


good evening

im working on a report that needs to be grouped and subtotaled by week #, starting 3-25-2020 which is week 13, how can i reset it to be week #1?
Go to the top of the page
 
Larry Larsen
post Apr 7 2020, 02:48 AM
Post#2


UA Editor + Utterly Certified
Posts: 24,573
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
My first early morning thought would be to create a table of dates & manufactured week numbers..
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
Gustav
post Apr 7 2020, 05:02 AM
Post#3


UtterAccess VIP
Posts: 2,239
Joined: 21-February 07
From: Copenhagen


You don't mention how the remaining rules for the week numbering are. For example, will you sometimes have 53 weeks per year?
Anyway, you may start here, assuming you have a week count for a year of 52:

CODE
SomeDate = #2020-03-25#
CustomWeekNumber = (DatePart("ww", SomeDate, vbWednesday) - 13 + 52 ) Mod 52 + 1

--------------------
Microsoft Office 365 (Access) MVP 2017 ->
Go to the top of the page
 
projecttoday
post Apr 7 2020, 05:06 AM
Post#4


UtterAccess VIP
Posts: 12,172
Joined: 10-February 04
From: South Charleston, WV


You could use a variable in the code or a textbox on the report with =1 in the control source.

--------------------
Robert Crouser
Go to the top of the page
 
Larry Larsen
post Apr 7 2020, 05:11 AM
Post#5


UA Editor + Utterly Certified
Posts: 24,573
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
When do you expect week(2) to start..?? (Sunday)
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
projecttoday
post Apr 7 2020, 05:57 AM
Post#6


UtterAccess VIP
Posts: 12,172
Joined: 10-February 04
From: South Charleston, WV


My thinking was about numbering the weeks in the input data, whatever dates might be in the data, starting with 1, something I believe you can do in the report without a separate table.

If this organization has a set fiscal year that is non-standard then I think I would use a table to store that.

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    27th May 2020 - 07:39 AM