Date Range Problem

[cnice9]

I have a few reports that need to run by a user selected date range. To do this I am simply using the following formula in Select Expert:
{Discipline_Log.IncidentDate} in {?Startdate} to {?Enddate}
This works fine unless the end user enters the same start and end date to show records for only one day,in which case no records are returned.
I have tried to use:
{Discipline_Log.IncidentDate} >= {?Startdate} and <= {?Enddate} but this returns an error that a number,currency amount,boolean,date,time or string is expected here.

I am using CR 9 in a ASP application.
Any suggestions would be greatly appreciated.

C.Nice
RealTime Information Technology Inc.

http://www.realitinc.com/

 

[synapsevampire]

Your syntax is wrong, try:

{Discipline_Log.IncidentDate} >= {?Startdate}
and
{Discipline_Log.IncidentDate} <= {?Enddate}

You might also use the Switch function to assure SQL pass through:

{Customers.DateField} =
switch(
{?Startdate} = {?Enddate}, {?Startdate},
{?Startdate} <> {?Enddate}, {?Startdate} to {?Enddate}
)

-k

 

[lbass]

The formula:

{table.datetime} in {?start} to {?end}

works when I test it in CR 8.0 using the same date if the parameters are set as dates, but brings back no records if the parameters are set as datetime parameters and the same datetime (with records in the database equal to this datetime) is set for both. If the parameters are set to datetime, no records were returned when using the following either, if the same datetime was entered for both:

{table.datetime} >= {?start} and
{table.datetime} <= {?end}

So, if you are using datetime parameters but are only interested in evaluating at the date level, you could change the parameters to date type.

Apologies, if this is irrelevant because you are working with dates...

-LB

 

[synapsevampire]

LB brings up a good point, and that is that you should be checking Database->Show SQL Query to see what is being passed.

Since you didn't bother to post your database type, I didn't bother to elaborate on your problem as I hate having to cover all of the bases because people are too hurried to describe their environment.

Sometimes Crystal gets confused when creating the where clause, so post specifics and I'm sure this will be an easy fix.

-k

 

[Kallen]

Okay, I am now officially loosing my mind. I am trying to take my running total formula and use it for a cross tab report.

I can see the formula within Crystal, but when I try and put it into a cross tab it does not show up as a field I can use for the cross tab.

I do have the formula within my report and have used formulas in the past for my cross tabs.


Any suggestions????????

Thanks

 

[lbass]

This should really be a new thread, but...

I don't believe it is possible to use running totals in an inserted crosstab, but you could create a manual crosstab.

Before doing that, you might want to determine whether you could use conditional formulas instead of running totals, since they would be available for an inserted crosstab.

But, assuming running totals are necessary, you could develop a manual crosstab by first inserting a group on what you consider your "row" field. Then you could create multiple running totals using the running total editor, and in the evaluation section, add the criteria specific to each column, and then reset the running total on change of group. You would then place the running totals in the group footer and suppress the details.

-LB

 

[cnice9]

Thanks for the responses especially lbass as he was right. Stupid me had the parameter set as datetime when all I needed to evaluate was the date. Thank you again for your assistance.

C.Nice
RealTime Information Technology Inc.

http://www.realitinc.com/