DateAdd function returning wrong dates!

[mmorala]

I have a DateAdd function that looks like this:

if {ACTIVITY.STARTDATE} > DateAdd("d",-1,{?Date}) and
{ACTIVITY.STARTDATE} < DateAdd(&quot;d&quot;,15,{?Date}) and
{ACTIVITY.CATEGORY} = &quot;Call Report&quot; then
1
else
0

When I run the report and use the date 8/8/2003 as a parameter, I am pulling dates from 9/25/2003. Why??

Thanks
Michael

 

[rhinok]

Unless this code is included in your record selection criteria, it doesn't have anything to do with the records being returned by the report itself. It looks like this code is used within a formula that generates a number to be used in summary operations.

Can you please post both your record selection criteria and the SQL generated by the report (Database|Show SQL Query)? We'll be able to better address your issue after seeing these.

 

[synapsevampire]

This formula has nothing to do with pull dates, it simply assigns a 1 or 0 to a formula if the dates are within some range.

To limit rows returned to Crystal, select Report->Edit Selection Formula->Record and use:

(
{ACTIVITY.STARTDATE} > DateAdd(&quot;d&quot;,-1,{?Date})
and
{ACTIVITY.STARTDATE} < DateAdd(&quot;d&quot;,15,{?Date})
and
{ACTIVITY.CATEGORY} = &quot;Call Report&quot;
)

Now this format may not pass the SQL to the database, so I would omit the dateadd function:

(
{ACTIVITY.STARTDATE} >= {?Date}
and
{ACTIVITY.STARTDATE} < {?Date}+15
and
{ACTIVITY.CATEGORY} = &quot;Call Report&quot;
)

Even this is dependent upon data types, so you may have to change the data type of the parm to match the filed type (probably not in this instance).

Check the Database->Show SQL Query to see what's being passed.

-k

 

[mmorala]

I am using the formula above {@CRProjCount} in a chart with these parameters:
On change of: {Username}
Show value: {Sum of @CRProjCount}

Here is my selection criteria:
{ACTIVITY.CATEGORY} = &quot;Call Report&quot; or
{ACTIVITY.CATEGORY} = &quot;Meeting&quot; or
{HISTORY.CATEGORY} = &quot;Call Report&quot; or
{HISTORY.CATEGORY} = &quot;Meeting&quot;

I have four graphs that I am using to show projected call reports and meetings and historical call reports and meetings broken out by sales reps.