Limit Records to Past Year 1

[Hillary]

I want to limit the records in my report via the Select Expert to the past year from today. I am using the Transaction_Date field which is a date/time field. There are a few "built-in" functions but none of the ones I've tried work (LastYearYTD, YearToDate).

Today, I want to see 4-23-02 (or 4-24-02) through 4-23-03.
Tomorrow, I want to see 4-24-02 (or 4-25-02) through 4-24-03.

How would I do this without having a parameter?

Thanks,

Hillary

 

[lbass]

This should work:

{TransActDate} in Date(Year(Currentdate)-1, Month(Currentdate), Day(Currentdate)) to CurrentDate

If you want it to start on April 24, 2002 instead of April 23, 2002, then substitute Day(Currentdate)+1

-LB

 

[mbarron]

One very slight drawback to lbass's solution would be leap years.


Another option would be the datediff funtion
datediff("d",{date.field},currentdate) in 1 to 365

Mike

 

[synapsevampire]

Mikes is accurate, but it won't pass SQL to the database, so performance will suffer.

Create 2 formulas:

@startdate
datetime(year(dateadd("y",-1,currentdate)),month(dateadd("y",-1,currentdate)),day(dateadd("y",-1,currentdate)),0,0,0)+1

@enddate
DateTime (currentdate, CTime("23:59:59&quot)

Record selection (report->Edit Selection Formula->Record):

{Orders.Order Date} >{@startdate}
and
{Orders.Order Date} <{@enddate}

This will be accurate and pass the SQL to the database.

-k

 

[synapsevampire]

Hillary: Mike was correct, when you hit Leap Year, the code you selected from Lbass will crash.

-k

 

[lbass]

Sorry about that--alternatively, how about just banning all work activity on February 29?

-LB

 

[synapsevampire]

<grinning>

I'm all for that, LB.

Yanno, I'm sure I've suggested the same code you suggested more than once, I always do it differently each time to break up the monotony

-k