Weekly Report to capture Year's data missing a week between run dates

laurastreng

Board Regular
Joined
Apr 1, 2004
Messages
58
Hello- I am running a report weekly that is capturing the previous year's worth of data. I am using this in my date field: >=Format(DateAdd("m", -12(Date()),"yyyy-mm-dd") This worked great on my first run. However, now that I am preparing to run this query a week later, if I run it as is, I will lose 7 days worth of data between the previous and present run. (ran it last on 3/31/09, planning to run it today, 4/8/09). I don't want to hard code a date anywhere if I can help it. Is there any way to get Access to capture those 7 days?

Laura S
Richmond, VA

I just had a thought but still not sure how to implement it: I could enter a field to the query prompting for date of the last run. How can I get the field that has >=Format(DateAdd("m", -12(Date()),"yyyy-mm-dd") to look at that date as well when pulling back the data? So I'd enter 3/31/09 as the last run date, then somehow, I need to tell Access to return data from 4/1/08 - 4/8/09 without hard coding dates.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

boblarson

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964
What do you want for the data? I'm not sure what you want. Do you want a year's worth up to and including the current date? Or a year's worth including the first of the Current Month?

If the latter then you can use:

>=Format(DateAdd("m", -12, (DateSerial(Year(Date(),Month(Date(),1)),"yyyy-mm-dd")
 

laurastreng

Board Regular
Joined
Apr 1, 2004
Messages
58
Thanks for your quick response! What I am doing is pulling in admissions that have occurred over the past 365 days, up to the current date. I run the query weekly. So today, while I still want to pull in the last 365 days of admits, I need to also include the difference between the current date I am running the report and when I last ran the report (3/31/09). In this instance, I'd need to also capture 8 additional days.

I don't know a whole lot about Access, so if there's a better way, I'm all ears. I usually determine what I want to accomplish then look around for a possible solution on the web and see if it'll work!

Thanks
Laura
 

boblarson

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964
Sounds like you really want the start of the month of the previous year up until today, correct?

I think that should look like:

>=Format(DateSerial(Year(DateAdd("y", -1, Date())),Month(Date()),1),"yyyy-mm-dd")
 

laurastreng

Board Regular
Joined
Apr 1, 2004
Messages
58

ADVERTISEMENT

I was trying to avoid pulling back duplicate rows, which I think is what would happen if I use the formula you suggest? Well, I guess I have duplicate rows anyway, the way I was going to do it. So if I use that formula, it'll go back to the beginning of the month I'm running it from the previous year up to the current date. April 8 2009 gets me April 1 2008 - April 8, 2009, April 15 2009 (once I finish my taxes) gets me April 1, 2008 - April 15, 2009. I just want to be sure that there won't be any days missed. Doesn't sound like it. Wait - I'll run it again then April 22, and April 29. The next time I'll run it is May 6, so if it is going back a year to the beginning of the month, so May 2008, how do I pull in April 30? Or any other stragglers should that last Wednesday fall earlier than the 29th?

Laura
 

boblarson

MrExcel MVP
Joined
Nov 14, 2008
Messages
1,964
You may need to store the date you ran it and then use THAT as the basis for your date range. What you are asking for is out of the normal scope of any normal SQL or function.
 

laurastreng

Board Regular
Joined
Apr 1, 2004
Messages
58
I found the answer I was hoping for! Wanted to post in case others need something similar:

>=Format(DateAdd("d",-364.2425-DateDiff("d",[Enter last report run date],Date()),Date()),"yyyy-mm-dd")
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top