Calcute Week Numbers Starting From 1 July Of Current Year
Feb 13, 2007
Hi everyone,
I want to calculate the week number of a date in a year starting from the 1 July (of the current year) - as this is the beginning of the Financial Year in Australia.
The starting day of the week is Wednesday.
I've been trying to think how I could use the Access DatePart Function in a query to calculate the week number from sales records.
I have had no trouble calculating the week number from the 1 Jan using the DatePart function. However, the function doesn't allow me to select a different date other than the first week of January.
So as a work around, I've thought of calculating the week number of 1 July in the current year, and just using the DateAdd function to add the week number to the week calculated by the DatePart Function.
Not quite working yet. Here's what I've come up with so far.
WeekNumberCount: DatePart("ww",[DateCalled],4)
I use the following to calculate the "my" starting week of the year:
I am creating a Make Table Query and calculating new fields based on current and prior year-end numbers. If the prior year-end number does not exist (Is Null), I want the use the current rate or calculate the change in rate. I have typed the below in the Field Box:
CHG_IN_PGM_RATE: IIf(([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE]) Is Null,([MASTER LIST CURRENT]![PTD_PM_PGM_RATE]),(([MASTER LIST CURRENT]![PTD_PM_PGM_RATE])-([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE])))
However, when running the query, I get the attached error message.
The screenshot will also show how the two tables are joined.
Want to use criteria in a date field to query data a week at a time. Open the query and have it ask for week starting MM/DD. Date input will normally use the Monday of each week and expect data back for Monday through Sunday
I am making a database which has a set of lessons which have been booked for a particular date.
What I want to do is be able to have a pop-up box ask you for a date for the start of the week. e.g. I want to find lessons from the week starting 02/03/2007 for 1 week (e.g. 2nd - 9th), so I type in the box "02/03/2007" and it comes up with records from that week.
I have tried this in the criteria box: [Enter date]+7 and =Date([Enter Date])+7
But nothing works. Also, I decided to add a record with todays date, and then use the criteria =Date(), but that didnt work either!!!!
I'm going to try using the year, day of year, hour & minute (24 hour clock) as a report number. It's set up in a field on a table. Right now I have....
Default Value =Format(Now(),"yyyhhnn") 'which works but not exactly how I would like
yy = Last two digits of the year y = Number of the day of the year (1 to 366) 'can this show three digits all the time? hh = Hour in two digits (00 to 23) nn = Minute in two digits (00 to 59)
For instance, right now for Jan. 10th, 2012, 1306 hours the result would be 12101304 which, for all intents and purposes works, but I would prefer the "day of the year" to always be represented by three digits and not just when it hits day 100 of the year.
I'm trying to add a couple of fields to the Contact database in Access 2010.
In the Contacts table, I created a field called "Sobriety Date" that has dates formatted like 12/27/1995
I am trying to add a calculated field called "Years Sober" which should be the current year minus the year in the 'Sobriety Date' field (1995 in the example above).
I have created a 6 search boxes with names Author, Published, Language, Title, Keywords and University. If I enter "A" in Author search field then it should return all the 6 fields which contains "A" in Author field. It worked well.
My Requirement :
The column published is the year. In search box of Published if I search for 2000 then it's returning all the 6 fields which contains "2000" in Published field.
But most of the people doesn't know the exact year in which the book is published so they need some favour like, If they search for 2000-2010 in Published field then it should return all the fields which are between 2000-2010 in the published field.
For this do I need to create 2 extra text boxes and name them as Starting year and Ending Year? or else we can achieve this in single text box as xxxx-xxxx?
I could have many periods in a future. What i want is to allow users to insert just first start year and lenght of period and table will be automatically updated based on these two values.
So user will insert Period_Start_Year and Length of Period, for example 2010 and 2 and table should be>
While i am writing this i realised that user have to specify also ending year, i do not want to have infinity.. I am thinking about form and update query but can't get it...
However, since the week beginning 12/26/05 crosses over both '05 and '06 (as week 53 and week 1), I get two groups of dates recorded for the week of 12/26/05 when I run my query.
Since my chart is based on weekly totals, I can't have two separate totals with the same 'week beginning' date.
How can I adjust my code to get one total group of dates for the week of 12/26/05?
I am using the format below to give me Year-WkNo and the leading zeros are not being put in so when a report is ran the yyyy-ww is not sorted correctly. Is there a way to get Jan 2007 week 1 to come out 2007-01?
YearMonth: Format([F Jobs Stats_sub2].[MaxofWork_Date],"yyyy-ww")
I have a table with sales by day. I want to display the data in a graph summarised by week but the period spans several years. If I format the date thus Format(MyField,"yyyy ww") then Access sorts the results thus 2003 1, 2003 10, 2003 11 but it should be 2003 1, 2003 2, 2003 4 etc.
How can I get Access to sort in ascending order correctly on the formatted date?
I analyse things by the date they are ordered by the office.
The date gets put into a spreadsheet and i import this into access for the querying.
I need to summarise it by weeknumber and year.
I use the following queries:
To get the week: Week Number: DatePart("ww",[DateOrdered],0,2) To get the year: Year: Year([DateOrdered]) To put it together: Weekyear: [Year] & "_" & [Week Number]
The problem is when the year changes.
The dates for week 1 for 2014 are between 30/12/2013 and 05/01/2014 and therefore straddles 2013 and 2014.
Therefore when the year and week number is put together the following results are given for that particular week:
I have big table that gets updated almost every day with new data. There is a date column. I have a Form where I can enter queries. I need to add a panel where I can spcify if the query should involve the data should involve the date from last week only, from the last 2 weeks, from the last month or if the query should involve the overall data.:confused:
It somhow need to be check what the date is today and then caclulate back.
I am setting up a database for a training centre, part of this includes registration The academic year I am working with runs over 36 weeks with 24 teaching weeks, 2 x 3 week holidays, 2 x 1 week reading weeks and the final 2 weeks for exams.
I aim to set the system up so that when a user opens the application, I get the username from windows, the day and time then open the correct register. That bit should be fine.
As I have 36 weeks I want to some how map the week numbers to the dates at the start of the term and also the holidays. There are 2 reasons for this, the first being so that when the user logs in the register only shows the current week instead of needing to search along all 24 or 36 entries, the other reason is so that when checking the whole register the holidays are not shown.
Does anybody have any suggestions or pointers as to how I can do this?
I am trying to write some VBA to convert a date into a week number.
My work colleagues do not not what system is used to define the week numbers. So I have gone through all their records for a few years and deciphered this pattern:
I need the code to define that there are 52 weeks in a year. The last sunday of Dec is the beginning of Week 1 for the following year. Except when it is a leap year where Week 53 exists. otherwise it only goes up to week 52.
Start day for the week is Sunday. End day is the Saturday.
Some examples to check against:
22nd December 2013 = Week 52 2013
29th December 2013 = Week 1 for 2014 ----
21st December 2014 = Week 52 for 2014
28th December 2014 = Week 1 for 2015 ----
20th December 2015 = Week 52 for 2015
27th December 2015 = Week 1 for 2016 ----
25th December 2016 = Week 53 for 2016
1st Jan 2017 = Week 1 for 2017 -----
Tried various methods already
iso 8601, wrong week start day
intWeek = DatePart("ww", datDate, vbSunday, vbFirstFourDays) Was right for 2013 but wrong for later dates.
Hi I am looking for a formula for my query which filters out only the orders raised in the current week.(5day week Monday to Friday) I did not want to enter dates manualy. Can this be done ? Can anyone help me with this.
I would like to ask for help on my current problem.
I need to input a date on a text box and another text box say txtweeknumber should determine the week number the date falls on the current month.
for example:
May 1, 2005 should fall on week 1 and txtboxweeknumber should say Week 1 May 9, 2005 should fall on week 2 and txtboxweeknumber should say Week 2 May 17, 2005 should fall on week 3 and txtboxweeknumber should say Week 3 May 25, 2005 should fall on week 4 and txtboxweeknumber should say Week 4 May 30, 2005 should fall on week 5 and txtboxweeknumber should say Week 5
Same goes for all months. Is there a function to determine which week number of the current month the input date falls?
I'm a bit new at all this but am trying to produce a query that filters on dates within the current week. Is there any way of doing this? I now how to do it for the day but at the moment I'm having to manually input 2 dates to get data in between them. Is there a command that will automatically detect the week and pull up dates within that?
Curretnly I have a query setup with Area, EventDate, & Complete. I want to be able to count each occurance of complete in current week. So far I have the following criteria under EventDate.
DatePart("ww",[EventDate])=DatePart("ww",Date()) And Year([EventDate])=Year(Date()).
So, this does in fact get me the records of current week, the issue I'm having is when an area has occurences daily. It will not bulk that area into a weekly count. I just need to be able to see how many occurances happen in the week total not daily. So my results could be.
I have a query with below formular which return's all records between Sunday and Saturday
Between Date()-Weekday(Date())+1 And Date()-Weekday(Date())+7
I can get this formular to work when it is looking at one date feild. (Date Received). I would also like it to look at the (date completed) feild so I get all records entered & completed in the previous week. I have tried putting same formular in the OR critiria as well but cannot get it to show all records at same time. What am I doing wrong ???
