Topics Search

How do I calculate a week number based on a field?

How do I calculate a week number based on a field?
Views: 23
Im trying to calculate a week number of a submitted report. Im guessing the way to go about this as I capture the date the user is referring to I can calculate which week number that date falls in? my question is how do I do it? I tried

=Format([TimesheetTableTemp]![Task Date],"ww")

where the task date is the location of the date but I get a prompt and then error
Sponsored Links:

Similar posts...


Calculating percentage (%)

Calculating percentage (%) Icon
I am wondering if it is realistic to create a query that will calculate the total % of a field based on four other field.


First field which will calculate the % of Q1
Second field which will calculate the % of Q2
the third field which will calculate the % of Q3
and the fourth field will calculate the % of Q4

AND the last field (the fifth one) should be the sum of Q1, Q2, Q3 and Q4 percentages and which should be equal to 100%

Converting dates into week numbers on graph

Converting dates into week numbers on graph Icon
I need to make a graph based on sale records. Every sale record has hours done field, date field and company department field who did the sale. On graph Y-axis I would need to see how many hours were done each week and then on X-axis week numbers based on dates.

As an example for April I would have Week 1 and every sale done between 02/04/2012 and 06/04/2012 would go into Week 1 category and so on (counting Monday to Friday), then Week 2 would have all records between 09/04/2012 and 13/04/2012 - so forth for every week.

Graph is not static of course so it wouldneed to update accordingly for every new upcoming sale as well.

to sum number of hours for each company department (just 2 of them) each week based on the dates I have?

Report with Running Sum by week

Report with Running Sum by week Icon
I have a report is built off of a query that contains the following fields:


I would like to be able to keep a running total of how much the employee has contributed each payroll based on his deduction. The payroll is bi-weekly so there are 26 periods in the year.

the payroll dedution amount is a calculated amount based on the annual contrbutuion/26

I was hoping that I could have a field on my report that would allow me to key in the week number and it would calculate the Sum of his dedcutions but I can not find a way for this to work.

Will this work or is there a better way to go about getting this information onto my report.

Count the number of Working Weeks

Count the number of Working Weeks Icon
I am working on a new project now and I need some help. Here is the situation:
When we enter the serial number of a battery, we have to check if it will be rejected or not. For example if the serial number was SP044000056: the third digit is for the year this means that the batteries was manufactured in 2010 and on the 44th working week.
So the problem now is to calculate the number of week between the today's working week and the working week in which the battery was manufactured.

I am able to extract the working week of the battery which is 44 and the year which is 2010 with the function MID.

How can I continue

Calcuating Values Pt/2

Calcuating Values Pt/2 Icon
I have one last question which is my last real hurdle with this database. I am going to attach a demo of the database in question. RuralGuy has helped me get this far, now I would like to know if it would be possible to do multiple calculate using the code he provided.

I mean with his code we are calculating the total PcsProduced based on the legenths*PPL. fields

I need to be able to calculate hours earned based on the PcsProduced field
this is calculated by taking the PcsProduced / SpecSPM that will = Hours Earned

I also need to calculate the Standard Hours. To calculate this I would take the Number Machine Run / 480 / 60

Basically I need to calculate multiple values, based on other calculated values. Possible

Financial/ Fiscal week number

Financial/ Fiscal week number Icon
Using the following in a query field, I can retrieve the week number from a field called 'StatusDate' where week 1 is always 1st April to 7th April regardless of the weekdays concerned

Volume of work per week

Volume of work per week Icon
I need a formula to calculate the sum of the total number sales per a given work week

Yes / No switch with odd or event numbers

Yes / No switch with odd or event numbers Icon
I am using an unbound form field to display week number based on a date field. I want to write vb code to run a query based on even or odd weeks.

Changing Week Start Day

Changing Week Start Day Icon
Access 2007, Crosstab query .Evening - We've just started opening our business on a Sunday and this little issue has just manifested itself!We record volumes of calls previously just Monday to Saturday and the call volumes are grouped by week number.

Which worked fine, now we open Sunday the Sunday values are pushed to the next week number. The customer wants all data for the week grouped Monday to Sunday, otherwise I'd just let the pushed week roll into the next week.

So for this week 16th to 22nd May, my week number would normally be 21. But the 22nd data is added to week 22
The current query field is:


This works. I've done some digging and found this possible suggestion

Val(Format([WorkingDate], "ww", vbMonday))

So my query field looked like this

Val(Format([CallDate],"ww", vbMonday))

But when I leave the field it adds [] round the vbMonday, and the query fails.
So any pointers so that my week number group goes from Monday to Sunday?

Date and week fields in table

Date and week fields in table Icon
I am having problems with date and week fields in a table. Since I start my week number at 1 for the school year as of August going until week number 52 in July, I have put the week number along with week start and end date in a separate table from the date table. Since I use several years worth of weeks, I have run into a problem when I try to tie the date and week fields together to sort records. Do I need to include the week number, week start and end date in the date table? Does anyone have any suggestions as to best how to do this? I will be glad to give more details to find a solution