Hi All

Struggling with a record formula. I need to return all records from a previous time period (4 weeks prior to the ?From Date) including the current time period as well if there are records in a current time period.

EG

Company    Current Period   Previous Period
Test1           0                 10
Test2           1                 10
Test3           0                 10

I need to return only "Test2" because "Test1" & "Test3" does not have any current period data.

I should see only

Company    Current Period   Previous Period
Test2           1                 10


My record select statement currently looks like

{@StartDate} >= ({?From Date} - 27) and
{@StartDate} <= {?To Date}

If I put an If statement in it will only return the "Current Period" but not the "Previous Period":

If {@StartDate} in {?From Date} to {?To Date} then
{@StartDate} >= ({?From Date} - 27) and
{@StartDate} <= {?To Date}

FYI
{@StartDate} = Date({datetimefield})

I need to do it this way as I am running a chart and cross tab.

Hope this makes sense.



Edited by Ashman - 12 Oct 2009 at 10:26pm

Thanks Lock, no go im afraid with the dateadd, it does the same thing. Im thinking maybe I need to return all values and filter at group level perhaps (i have group level for the period - either current or last 4 weeks and company).

Thanks again Lock,

I will give it a go - the DATE(table.field) IN ({?From Date} - 27) to {?To Date} does work, however it returns all records from activity from the past 4 weeks for all companies. Basically what I am after is, if a company has had activity within the date range then it will also return their activity for the past 4 weeks.

Cheers