Find the Number of week in each month for a Particular Year in access query
Hi There,
I like to find how many weeks exist in each month for a year. I have query for each month. I have query in access (summing the qty each month for each Item) for each month January through December from a table that has Qty for each item for each day.
The query has the year, month, Total Quantity. For example the Jan query would have the following fields
Yr Mth Qty Item
2018 1 10000 ABC
I would have to find the weekly quantity for that I would have to divide this qty by the number of weeks each month has in a year and it differs each year.
so I like to see the query result with another field MaxWk ( that will tell the total week in that month for the year)
I tried the datediff funtion doesn't work .I tried MaxWk= DateDiff("ww",1/1/[yr],1/3
Any idea how I can do this?
Thanks
I like to find how many weeks exist in each month for a year. I have query for each month. I have query in access (summing the qty each month for each Item) for each month January through December from a table that has Qty for each item for each day.
The query has the year, month, Total Quantity. For example the Jan query would have the following fields
Yr Mth Qty Item
2018 1 10000 ABC
I would have to find the weekly quantity for that I would have to divide this qty by the number of weeks each month has in a year and it differs each year.
so I like to see the query result with another field MaxWk ( that will tell the total week in that month for the year)
I tried the datediff funtion doesn't work .I tried MaxWk= DateDiff("ww",1/1/[yr],1/3
Any idea how I can do this?
Thanks
Last Comment
If you don't mind putting in the whole data (i.e. 1/1/2018), then you can use:
Date2: DateDiff("ww",[Start Date:],[End Date:])
Where [Start Date:] and [End Date:] will cause the pop-up parameter boxes. Also, I don't know about anybody else, but I like to add a : at the end of my parameter prompt.
Date2: DateDiff("ww",[Start Date:],[End Date:])
Where [Start Date:] and [End Date:] will cause the pop-up parameter boxes. Also, I don't know about anybody else, but I like to add a : at the end of my parameter prompt.
"weekly quantity" and "weeks per month" is not related, as only February in common years has a length of an integer count of weeks - and these will in most years be split over five calendar weeks.
So, it is not very clear, what you are after. Week periods, calendar weeks, decimal weeks, average weeks, or?
So, it is not very clear, what you are after. Week periods, calendar weeks, decimal weeks, average weeks, or?
It might be useful to know what answer you expect to the question "How many weeks in January 2018?"
It could be 4 (whole weeks), or 4.6 (four weeks and three weekdays) or 4.42 (number of 7 day periods) or 5 (number of whole and partial weeks)...
It could be 4 (whole weeks), or 4.6 (four weeks and three weekdays) or 4.42 (number of 7 day periods) or 5 (number of whole and partial weeks)...
ASKER
Hi Paul,
It would be 4 (whole weeks).
Thanks
"It might be useful to know what answer you expect to the question "How many weeks in January 2018?"
It could be 4 (whole weeks), or 4.6 (four weeks and three weekdays) or 4.42 (number of 7 day periods) or 5 (number of whole and partial weeks)..."
It would be 4 (whole weeks).
Thanks
"It might be useful to know what answer you expect to the question "How many weeks in January 2018?"
It could be 4 (whole weeks), or 4.6 (four weeks and three weekdays) or 4.42 (number of 7 day periods) or 5 (number of whole and partial weeks)..."
ASKER
I tried Format(DateSerial(Year([YR
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
and got the result I was looking for
I seriously doubt that. It's just the a weeknumber of the ultimo of a month.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
DateDiff("ww",DateValue("1
For one, you weren't telling access what you were providing as a date. Dates are delimited with # signs: #1/1/2018#, but you can't include parameter brackets in it. You have to build your date as a text string and then get the datevalue of your date string.