Report auto-generate biweekly for current month data up to today ?

[qjade]

Hello friends,
I currently have 3 querries that run on a few tables according to date range. A report is then generated base on the results from these three separate querries.
What I like to happen is for my report to be auto-generated on a biweekly and end-of-month basis so that it can be emailed to my group of designated recipients. My report, however, can not be accurate if my querries are ran weekly or biweekly so that I can run my report immediately after them.
My questions:
1) How do I auto-fill in the date range for my querries so that they can run weekly/biweekly and month-end without my intervention?
2) Relating to question one, how do I just grab data for the current month up-to-date? What I mean is how do you specify the range of our current month, September 2004, on a weekly basis (ex: querries running every Saturday on 9/4, 9/11, 9/18, 9/25, and a summary one at the end of the month)?

Please, any input will be greatly appreciated. Let me know if I can make any more sense from the jumbo of non-sense above. Thank you in advance.

 

[dhookom]

Are your 3 queries action queries? What specifically do you have an issue with:
- running an action at a particular date
- setting criteria in queries that reference the current date
- setting any criteria in queries

Can you be more specific with your question? Maybe limit your question to one or two issues.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[qjade]

Thanks for the reply. My main problem is how to auto populate the date criteria of my querries to the previous month that had just passed when my querries are run the first of the month. Also, is it possible for querries to be run without my intervention? Thank you for giving me a hand with this.

 

[dhookom]

To pull the records from the previous month for a field named OrderDate, use something like:
WHERE Format([OrderDate],"yyyymm")=Format(DateAdd("m",-1,Date()),"yyyymm")

What do you mean by "without my intervention"? You can create code that will run queries based on some event. There are also scheduling applications that can open and mdb. The command line can reference a macro that runs the queries.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[qjade]

dhookom,
After typing in what you have suggested above, I am getting an "Overflow" error. Is there another easy way to input the date range of the previous month into the Criteria? Also, by "without my intervention" I mean that I am having a macro auto-emailing reports based on these querries. However, the data are not very correct if I can not find a way to auto-change the date criteria of those querries every month when my reports are run. By the way, my Date field format is yyyymmdd, if that makes a different. Hope this make better sense as I am terrible in explaining my needs. Thanks again.

 

[dhookom]

It makes no difference what your date format property is. It makes a world of difference if you are storing a date value versus either a numeric or text value that is not a date. What is the data type of your "date" field and are any value possibly Null?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[qjade]

The date field is actually a "Number" type (sorry, I should have checked it earlier). No value in it can be null. Hope this help you in helping me. Thanks.

 

[dhookom]

Try:
WHERE [OrderDate]\100=Val(Format(DateAdd("m",-1,Date()),"yyyymm"))


Duane
MS Access MVP
Find out how to get great answers faq219-2884.