I have a large report that runs a six weeks average based on the last six weeks of production. Our fiscal year starts November 28th and our weeks starts on Monday.
I have used the following format to start the week on Monday
Week1: (DatePart("ww",[EntryDate],2))
To set up the fiscal year I used the following format in my query.
FiscalYear: IIf(Format([EntryDate],"mmdd")>"1127",DatePart("yyyy",[EntryDate])+1,DatePart("yyyy",[EntryDate])) . The criteria is 2006
I have a Weekending table that I have all the weeks listed including the weeks in calendar 2005 that are included in our fiscal 2006 that I have linked to my query.
When I run the report, it will not include weekending 1/1/06 (Week 1). It is totally eliminated. There is probably a conflict in the above formats, but I am not knowledgable enough in Access to figure it out. I have been searching this site without success. I would appreciate any help I can get. Last week I had to do the reports manually because I couldn't get the works to run right.
Hi, I will really appreciate ur help. I work on Access-2000 database and have a problem. I need to get the number of the week. So i used the funktion as a standard value as following: "=((Monat(Datum())*30,5-Tag(31-Datum()))/7)" It shows me for today 39 instead of 44. Could u please help me and tell me why this funktion doesnt work? Thanks in advance
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 would like to display the date and week number on one of my forms but have no idea about dates and how to code this. can some one help? id appreciate it it beyond me :confused:
Is it possible to query using the week number in the year as criteria. For example instead of putting 13/05/05 to 20/05/05 can we use week 12 - the idea is to save on keying info in each time.
Help!! I have 2 fields in a table one for a year and one for a week number that data is being collected against. I need to change these to a month and year for the report. Is there a way to do this??
I am trying to avoid the creation of a lookup table where I know I should be able to calculate this. I need to determine the start and end date of a week number. I did find this post from 2002: http://www.access-programmers.co.uk/forums/showthread.php?t=33116&highlight=start+end+dates+week but it doesn't seem to work.
What I am trying to do is show all projects that were opened at the start of a week number. For example, week 2 is from 1/7/2008 and ends on 1/13/2008 (provided that your week starts in the week that 1/1 occurs and that your week starts on Monday). I need this start date so I can show all projects that started on or before 1/7/2008 and were in an open status.
If I can't calculate this then I will have to create a table and relate the query to it.
When using the Format:'yyyy mm dd' in access queries. When trying to show a week is there a way to show the date range instead of week number.
Week 01 = Jan1-7. If the results are only week numbers that makes it hard for people to understand when it actually is. Bottom line is: I would like to convert Week number to actual date range. Can anyone help with this?
The 1st/2nd Jan of this year were treated by most people as still being week 53 of 2004, and week commencing 3rd January being week 1 of 2005. In VB I have a formula to do this:
I have a summary form with the company name,week number, week-ending automatically populated. e.g. this week is week 4 and users enter records for week 4 as the week number is automatically generated. Basically the system will just recognise today's date and generate the week number. Problem is I am trying to display previous week records and I do not know how to filter it as my week number is generated automatically, week by week. All the other forms are linked to this week number. How can I display records for a specific week that I want? say, I want to display all records for week2, etc. I am dumbfounded as I am on a learning curve with Access.
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 built the below query to count the number of ID's for each week. The problem is that if one month ends mid-week it is counting the next month's IDs in the first month. I need to count the number of IDs within each week by month. I have the first qryGroupbyWeek that assigns a week number to each date then the below query to aggregate.
Code: SELECT qryGroupbyWeek.Week, qryGroupbyWeek.[Approved Date], qryGroupbyWeek.ID FROM qryGroupbyWeek WHERE (((qryGroupbyWeek.[Approved Date]) Between [start] And [end])) ORDER BY qryGroupbyWeek.ID;
I've been asked to edit a current report that lists our production in week number order. I need to look at way we can 'flag up' orders that are within a 4 week period from the current date.
For example,
10 Orders in total in our database. 5 of which are due the drawings back within 4 weeks from today (26/02/15). I'm looking for a way for the report to show the 5 orders as priority, either by formatting the orders in bold, a different colour or under their own heading/group.
I've created a report based on a query that shows me the jobs that have been added to tblJobs between two selected dates.
The report works fine and displays all the information I need, however other than being sorted by day of the week i.e. Monday, Tuesday, Wednesday etc. The records just run on one after another.
I want to create a page break, so that a full week is shown on a single page before then forcing a new page when it moves in to the next week.
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?
What I would like to do is get a count of the product sold and view it over a ten week period in this case week one starts 26th June.
Wk1, Wk2, Wk3 ....
x 10 5 15 etc y 20 4 12
in the format above but I am not sure how to achieve this, I have the following:
SELECT COURSEBK.[COURSE-DSName], COURSEBK.From, COURSEBK.Description, Count(COURSEBK.[COURSE-DSName]) AS [CountOfCOURSE-DSName] FROM COURSEBK GROUP BY COURSEBK.[COURSE-DSName], COURSEBK.From, COURSEBK.Description HAVING (((COURSEBK.From)>=#6/26/2005# And (COURSEBK.From)<=DateAdd("ww",+10,"26/06/05"))) ORDER BY COURSEBK.From;
(the above is working despite access changing the date format between the hashes)
