Use a Parameter or Formula in the Date() function

[Former Member]

I am trying to filter a result set utilizing the Date() function. I am trying to get all of the data between June 1st of a given year, and a parameterized date. Here is the formula

{ReportStatic.rpsActualSaleDate} >= Date({@fPrevioustoLastYear}, 7, 1)

and {ReportStatic.rpsActualSaleDate} < DateAdd("yyyy", -2, {?pDateEnd})

and {ReportStatic.rpsSaleStatus} = 1

The value of fPrevioustoLastYear is '2008'

The report is returning 0 records. I think it is because the Date() function isn't recognizing the formula (fPrevioustoLastYear) as a valid input. However when I "Save and close" the formula editor doesn't find any problems with the formula.

What should I be doing differently?

[Former Member]

If {@fPreviousToLastYear} is in fact 2008, I don't see an issue (with the possible exception that it should be a number, not a string; convert with CInt()). Please post your formula for that field.

Also, I have to ask what the value of {?pDateEnd} is. If it's like today, no records will fit the criteria because you'd be checking if the date was between 7/1/2008 and 3/29/2008 (you're subtracting 2 years from the parameter), which is a null set.

HTH,

Carl

[Former Member]

Carl,

You are correct that it was returning a null set. I should have been subtracting 1 and not 2.

Sometimes all it takes is a second pair of eyes. Thanks for help.