Select all records if field is curret month

[rmiller11]

What I am trying to do is if the date in POCMonths.PocMonth month of current date is = to print time then I want >= and <= of POCMonths.PocMonth records but only if there is a current entry for the print time month. For th elife of me I can't figure out how to do it. I can get >= POCMonths.PocMonth and I can get <= POCMonths.PocMonth (See Below) but I can't get all the records if it contains the current month. I don't know if I need some kind of variable or what. I want to get all the PTD stuff then all the stuff in the future months as well as current month but only if there is an entry in the current month. Confused?

If DateDiff('m',CurrentDate,{POCMonths.PocMonth}) = 0 then @sales

 

[synapsevampire]

What does this mean:

&quot;if the date in POCMonths.PocMonth month of current date is = to print time &quot;

If it's the same month as the current month and = to print month? The latter might only differ if a report runs at the end of a month and into the next.

Anyway, I'm guessing that there were some typos in there...to get the sql to pass (which your solution won't, which means poor performance) and to get the correct results, I'd suggest the 2 formula method:

@startdate:
DateTime(Year(CurrentDate),Month(CurrentDate),1,0,0,0)

@enddate:
If {?Predefined Date Range} = &quot;Current Month&quot; Then
If Month (CurrentDate) = 12 then
DateTime(Year(CurrentDate),12,31,23,59,59)
Else
DateTime(Year(CurrentDate),Month(CurrentDate)+1,1,23,59,59)-1

In the record selection criteria place something like:

(
{POCMonths.PocMonth} >= @startdate
and
{POCMonths.PocMonth} <= @startdate
)

Now if you want a rowset for all future dates if this month does not contain data, copy the current report to another file, then bring it in as a subreport, then change the subreports record selection criteria to:

{POCMonths.PocMonth} > currentdate

Now conditionally suppress the section the subreport is in using:

recordcount <> 0

And you can suppress everything in the Main report using:

recordcount = 0

So it will either display the current month if there are rows, or display future rows.

You're generally better served to supply some example data and expected output.

-k

 

[lbass]

I can't tell for sure whether you want to retrieve records both before and after the POCMonth.POCMonth if it = the current month. What does PTD mean? PeriodToDate? Anyway, you could try this:

1-Create a date range parameter to define the outer range of the date period under consideration, assuming the POCMonth meets your criterion, and add to your record select statement.

{POCMonth.POCMonth} in ?DateRange //I'm not sure what datatype the field {POCMonth.POCMonth} is, so you might have to adjust this

Or you could set up a specific month parameter ?POCMonth and a second parameter ?Period with options &quot;<=&quot;, &quot;>=&quot;, or &quot;both&quot; that allows the user to determine which date periods to view and set up an if/then statement for the record select.

2-Create a formula {@InCurrentMonth}, place in detail field, and suppress the field:

If DateDiff('m',CurrentDate,{POCMonth.POCMonth}) = 0 then 1 else 0

3-Let's assume you are grouping on {Salesperson}. Create a formula {@SumInCurrentMonth}:

Sum({@InCurrentMonth},{Saleperson}) >= 1

4-Place this formula in the group select statement. This should select all records within the selected date period for salespeople who have some sales during the current month.

5-Use running totals to calculate summaries that cut across Salespeople, since the usual summaries (counts, etc.) will include underlying data from before the group select.

-LB