Problem with a weekly query at month end

[Gryphoune]

We run weekly reports to show totals by property for recovered and lost revenue. The person I took this over from built the following queries to give the weekly sums but when the end of the month is during the week I get no data on the report. Is there a better way to build this query/queries so that I get accurate weekly data even at month end? I am assuming that the problem is with Week Ending parameters but I'm not sure how to change them or if another parameter is required to bridge the month end. Or should it require input for begin and end date? Thanks for anything you can help with.

[John_G]

I think the problem is with the where clause for Month -

It is using month(now()) as the criteria, meaning that the month of the expression it is checking (which I can't understand!) must be the current month. So, if you run the query on the first of the month, there will be few if any records that match.

[eki einstein]

creating a form thats input begin and end date its good idea that was usual ms acces programers do !, but in my Opinion Ms Acces Advance Filter are give more to user and help programer acces do much eassier to using it(the Query), in advance filter there is so many way to user to decide what they want to create/run the Query it self(Monthly,Quaterly,Year,between,etc) for weekly sorting group they can use between. and i think its better than we wrote it in the Criteria field that some times can create a Bug when the query run for many goal.

this some pictures from my apps that I using Advance Filter as advantage to user to using it. and what my client say about it are "its are exactly what they need."

what you need is creating a form then create subform containing the Query and of course you can adding a print button to print the Query or link it to a report that you create from the beginning.

Sorry if i had bad english because im an indonesian www.ekieinstein.com

[June7]

What is correct according to your business rules? If the summary is supposed to be weekly, then basing the summation on week end date does not consider the month end (nor year end). Do you want to break the crossover week by the last day of month and report partial week in each month? One approach might be to calculate a year/month/week and group on that.

YMW: Year([datefield]) & Month([datefield]) & DatePart("ww", [datefield])

[Gryphoune]

What we need is a strictly week report regardless of the month. This basically outputs the weekly production of the team. There is a report with subreports that calls up the recovered and loss totals for each property but this query determines what dates to pull the data from. I run the report every Friday afternoon and our week runs Sun-Sat.

"If the summary is supposed to be weekly, then basing the summation on week end date does not consider the month end (nor year end)."

Does this mean I can remove the month and year criteria and it should work properly or do I need to add an additional criteria to pull just the last week?

[June7]

If you want a strictly weekly summary and calculate the week end date for each record based on relevant date field and apply filter to that calculated date, why would separate year and month criteria be needed?

[Gryphoune]

Thanks June7. I was kinda thinking that but the report actually pulls data for previous weeks in the month so at the beginning of the month the report can't pull data for the previous month. Here's a screen of the report design.

[eki einstein]

if this strictly report as you mention i think the best solution it use start and end date ,my oppinion is you not need seperate Month criteria and year field just like june7 say

[June7]

Suggest apply filter criteria to the week end date (FROM/TO or >).