Date Selection Problem

[khekking]

Using:
SQL Database
Crystal Reports 11.5.1


Using the following formula I'm getting returns outside the requested scope for {LastRCSContact}.

Can someone take a look at this and let me know if additional information is needed and what I need to do to fix my formula.

Many thanks in advance.

Ken

if {KH_LastRCSActivityDate.LastRCSContactDate} in dateserial(year(currentdate), month(currentdate)-7,1) to dateserial(year(currentdate), month(currentdate),6)-1
and {Invoice_Detail.INVDTE_32} in dateserial(year(currentdate), month(currentdate)-13,1) to dateserial(year(currentdate), month(currentdate)-7,1)-1
and {Invoice_Detail.PRTNUM_32} like "RENT*"
then {@Extension Price}
else 0

 

[IanWaterman]

For today
dateserial(year(currentdate), month(currentdate)-7,1) returns 1 Aug 2012 and
dateserial(year(currentdate), month(currentdate),6)-1 returns 5 Mar 2013

Is that what you are expecting?

Try
{KH_LastRCSActivityDate.LastRCSContactDate} >= dateserial(year(currentdate), month(currentdate)-7,1)
and {KH_LastRCSActivityDate.LastRCSContactDate} <= dateserial(year(currentdate), month(currentdate),6)-1

Personal preference I never use In X to y for date ranges

Ian

 

[khekking]

Ian,

Thanks for the quick response. I had a typo on the date select formula list above (typing to fast). The corrected formula is below:

if {KH_LastRCSActivityDate.LastRCSContactDate} in dateserial(year(currentdate), month(currentdate)-7,1) to dateserial(year(currentdate), month(currentdate)-6,1)-1
and {Invoice_Detail.INVDTE_32} in dateserial(year(currentdate), month(currentdate)-13,1) to dateserial(year(currentdate), month(currentdate)-7,1)-1
and {Invoice_Detail.PRTNUM_32} like "RENT*"
then {@Extension Price}
else 0

I tried your >= & <= solution. Unfortunately, the problem persists. Ideas?

Thanks for your help!!!!!

Ken

 

[pmax9999]

Using the revised code, it should be returning records where {KH_LastRCSActivityDate.LastRCSContactDate} is between 1st and 31st Aug 2012 and {KH_LastRCSActivityDate.LastRCSContactDate} is between 1st Feb 2012 and 31st July 2012. Is that the intent?

When you say the records returned are "outside the requested scope" can you give an example please.


Regards
Pete.