troubleshooting Question

Find the Number of week in each month for a Particular Year in access query

Avatar of Sivasan
SivasanFlag for United States of America asked on
Microsoft Access
8 Comments1 Solution59 ViewsLast Modified:
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/31/[yr],2), just returns 0  
Any idea how I can do this?

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros