need to add past 7 dates

[Former Member]

I have this working so far

order#   Current date     Non Current

123       102214

234                                   101214

Now they want a new col for past 7

so we would have this:

order#   Current date     Non Current      past 7

123       102214

234                                   101214

456                                                        102014

for done so far

formula to get the current date: (CD is the alpha date field)

If  {@dd} = currentdate then

{OEORH1.OHORDD}

then i display this in the col for current

all the others i display in the non current

how to check if past 7

how to check if @dd is in currentdate - 7  

syntax wise this is not correct.

[Former Member · Accepted solution]

Hi,

Try this:

If  {@dd} = currentdate then

{OEORH1.OHORDD} else

if {@dd} < datevalue(cuurent date) - 7 or {@dd} < dateadd("d",-7,today) then past 7

else Noncurrent.

I am not sure just given A small trick.

Helpful Link: Crystal Report Tips and Tricks - Part 2

Thanks,

DJ

[Former Member · Accepted solution]

Thanks Dhananjay, actually they have changed a bit the requirements.

I would like to code a simple thing. to select records if the dates are 7 days back thru 1 day back.

They want 3 reports now. the first is current date. that is easy.

the second is 7 days back but not the current date included.  I need something like this,this one fails.

{OEORH1.OHORDD} > {@current date 2} - 6

and < {@current date}

[Former Member · Accepted solution]

Hi,

Try this:

Step1. ({OEORH1.OHORDD} > {@current date 2} - 6) or ({OEORH1.OHORDD} < {@current date})

This works either first condition or second condition.

Step2.

({OEORH1.OHORDD} > {@current date 2} - 6)

and ({OEORH1.OHORDD} <  {@current date})

Both conditions pass then this condition executed.

Thanks,

dj

[abhilash_kumar · Accepted solution]

Hi Paul,

Use this code:

{OEORH1.OHORDD} IN [currentdate-8 to currentdate-1]

-Abhilash

[Former Member · Accepted solution]

Abhilash, i get an error

'a number range is required here' it has the cursor

at the 'and'.

{OEORH1.OHORDD} IN [currentdate-8 to currentdate-1] and

[abhilash_kumar · Accepted solution]

What is the datatype of the {OEORH1.OHORDD} column?

-Abhilash

[Former Member · Accepted solution]

this is character.

[abhilash_kumar · Accepted solution]

OK, and does it come in this format : 102214 (mmddyy)?

You would need to convert this to date.

E.g:

stringvar s := "102214";

numbervar mm := tonumber(Left(s,2));

numbervar dd := tonumber(Mid(s,3,2));

numbervar yy := tonumber(Right(s,2));

cdate(yy,mm,dd) IN [currentdate-8 to currentdate-1] ;

-Abhilash

[Former Member · Accepted solution]

I wrote something spooky that almost works.

the last one (7) bring in the row but does not display the date. but i checked the order and it is for the 10-16!

if i take out the 7, it has from 10-17-10-22.

why would it do this?

If  {@dd} = currentdate-1 then

{OEORH1.OHORDD}

else

If  {@dd} = currentdate-2 then

{OEORH1.OHORDD} else

If  {@dd} = currentdate-3 then

{OEORH1.OHORDD}

else

If  {@dd} = currentdate-4 then

{OEORH1.OHORDD} else

If  {@dd} = currentdate-5 then

{OEORH1.OHORDD}

else

If  {@dd} = currentdate-6 then

{OEORH1.OHORDD}

else

If  {@dd} = currentdate-7 then

{OEORH1.OHORDD}

[Former Member · Accepted solution]

the dates are on yyyymmdd format

[abhilash_kumar · Accepted solution]

In that case, this should work:

stringvar s := "20141022";

numbervar mm := Mid(s,5,2);

numbervar dd := Right(s,2);

numbervar yy := tonumber(Left(s,4));

cdate(yy,mm,dd) IN [currentdate-8 to currentdate-1] ;

-Abhilash

[Former Member · Accepted solution]

its not compiling it says a number is required here on Mid

stringvar s := "20141022";

numbervar mm := Mid(s,5,2);

numbervar dd := Right(s,2);

numbervar yy := tonumber(Left(s,4));

cdate(yy,mm,dd) IN [currentdate-8 to currentdate-1] ;

[abhilash_kumar · Accepted solution]

Sorry, the code should be:

stringvar s := "20141022";

numbervar mm := tonumber(Mid(s,5,2));

numbervar dd := tonumber(Right(s,2));

numbervar yy := tonumber(Left(s,4));

cdate(yy,mm,dd) IN [currentdate-8 to currentdate-1] ;

-Abhilash