process for previous full month

[Former Member]

I have a report that should run for the previous month. I will setup on the scheduler to run the 1st of each month.

For the date select the date is a Number in YYYYMMDD format. I want the entire previous month as the record select.

[DellSC · Accepted solution]

1.  Create a formula to get the first day of the previous month in the correct format.  It will something like this: (I'll call this {@StartDate})

DateVar chkDate := DateAdd("m", -1, CurrentDate);

StringVar result: = ToText(Year(chkDate), 0, "") + ToText(Month(chkDate), "00" ) + "01";

result

2.  Create another formula to get the first day of the current month (in case the report gets run on a date other than the 1st...):  (I'll call this {@EndDate})

ToText(Year(CurrentDate), 0, "") + ToText(Month(CurrentDate), "00") + "01"

3.  In the Select Expert, edit the formula and do something like this:

{MyTable.DateField} >= {@StartDate} and

{MyTable.DateField} < {@EndDate}

-Dell

[Former Member · Accepted solution]

Hi Dell, thank you. I entered the first formula but get an error on this point.

DateAdd("m", -1, CurrentDate)

"A date is required here'

[DellSC · Accepted solution]

Oops!  My mistake - change "DateVar" to "DateTimeVar".

-Dell

[Former Member · Accepted solution]

it has an error on @StartDate

that a number is required there. but the col is number

[DellSC · Accepted solution]

So the column you're comparing to is a number, not text.  If that's correct, make the following changes:

In the {@StartDate} formula:

NumberVar result: = ToNumber(ToText(Year(chkDate), 0, "") + ToText(Month(chkDate), "00" ) + "01");

In the {@EndDate} formula:

ToNumber(ToText(Year(CurrentDate), 0, "") + ToText(Month(CurrentDate), "00") + "01")

-Dell