I am trying to generate 2 monthly reports: One comes out on the first of the month and one comes out on the 16th of the month. I have a Start Date field. I'm trying to figure out how to filter the rep...
If these two reports are the only place that you care about the Numerical Day in the field [Start], then a simple solution is to use Report Formulas.
Here is a screen shot of it being set up. This assumes that [Start] is Date field and NOT a Date/Time field. If this assumption is false then inside the formula you first have to convert [Start] to a Date and then use Day() on the result.
------------------------------ Don Larson Paasporter Westlake OH ------------------------------
Thank you, Don. I've never worked with Report formulas before so I've learned something new. I can't figure out how to make it work for the report I need on the 1st of the month because it has to be >20 two months ago but <5 last month. Any suggestions?
I read your date ranges incorrectly the first time. I am going to assume that you will have two separate reports on the 1st or 16th, export the data and then not use those reports again.
Build custom dates based upon Today() that will be the correct dates for your report on that day using the ToDate() function. ToDate("1/30/00") will give you January 30th of 2000 as a date field that you can report on. You just need to build the month, day and year pieces formulaically.
Take Today() and move it to the calendar month you want using AdjustMonth().
Then strip out the value of the Month() or value of the Year()
Change them to Text values using ToText() which is what ToDate() requires as the argument of the function
String them together to build the date that you want using quotes, "", and ampersands , &, to get the text string you need.
Here is an example
That should give you the 21st day of the month from two months back. Edit and repeat this for your Report Stop Date.
------------------------------ Don Larson Paasporter Westlake OH ------------------------------
I'm sorry. I'm really confused. I don't understand why I need to make it a text? And then how do I use that formula to filter my report? I can't figure out how to compare my job start dates to the date generated in that formula so the correct jobs show up on the monthly reports.