Date Time Formula in Select Expert

[RastaDog]

I using Crystal 8.5 and an Oracle database

I wanted a users to run a sales order report twice per day. The first time frame would be the previous date at 4:30:01PM to current day at 12:00:00PM. The second time frame would be current date at 12:00:01PM to 4:30:00PM. That would cover all Orders for a 24 hour period.

I added to 2 parameters with Date and Time.... and put them in the Select Expert, 'is between' {?From_Date} and {?To_Date}
The report runs fine......

I would like to make it easier for the users (not to type in the time 12:00:01PM all the time)

I added 2 parameters. Run_Date (formatted to Date) and First_or_Second_Run( users puts in 1 or 2)

Wrote formulas.....

'From_Date1' ({?Run_Date} -1) + TimeValue (04,30,01 )
'To_Date1' CurrentDate +
TimeValue (12,00,00 )
'From_Date2' CurrentDate
+ TimeValue (12,00,01 )
'To_Date2' CurrentDate +
TimeValue(04.30.00)

'From'
If{?First_or_Second_Run} = 1 Then

{@From_Date1}
Else
{@To_Date1}

'To'
If{?First_or_Second_Run} = 1 Then
{@From_To2}
Else
{@To_To2}

If I just display @Form_To1 , @Form_To2, @To_Date1, @To_Date2 they look fine. (2/14/2010 4:00:00PM)

If I add the 2 formulas to the Select Export as 'is between' @From and @To the report just sits there and takes forever to run (if it runs at all)

Did I do something wrong?? or is there a better way to do this???

Thanks

 

[jdemmi]

Shouldn't your FROM parameter formula read

If{?First_or_Second_Run} = 1
Then
{@From_Date1}
Else {@FROM_Date2}
<-- you had TO_DATE1 here

and shouldn't your TO parameter formula read

If{?First_or_Second_Run} = 1
Then {@To_Date1}
<-- Else
{@To_Date2}



-- Jason
"It's Just Ones and Zeros

 

[RastaDog]

Thanks.... I just had a type-o on my posting. The actual report has the correct If statement.

Somehow when I use @From and @To in the Select Expert, the report just sits there running.

 

[jdemmi]

Can you copy and paste your selection formula here?

-- Jason
"It's Just Ones and Zeros

 

[RastaDog]

-- This is the one that has problems
{NA_SO_ORDER_CONF_VW.GL_CMP_KEY} = "NA" and
{NA_SO_ORDER_CONF_VW.SO_HDR_CRTDT} in {@From} to {@To}

-- This is the one with regular Date Parameters... works fine.
{NA_SO_ORDER_CONF_VW.GL_CMP_KEY} = "NA" and
{NA_SO_ORDER_CONF_VW.SO_HDR_CRTDT} in {?FROMDATE} to {?TODATE}

 

[jdemmi]

Admittedly I had not tried what you're doing...so I setup a basic report and tested it. It works fine for me, given these circumstances..

1. "PERIOD" (String Parameter -- acccepts value of 1 or 2.)
2. "FROMDATE1" (Formula with the following value inside

DateTime (2009, 01, 01, 00, 00, 00)

3. "FROMDATE2" (Formula with the following value inside

DateTime (2010, 01, 01, 00, 00, 00)

4.. "TODATE1" (Formula with the following value inside

DateTime (2009, 01, 31, 00, 00, 00)

5. "TODATE2" (Formula with the following value inside

DateTime (2010, 01, 31, 00, 00, 00)

Select expert set as follows...

IF {?PEROD} = "1" THEN {TABLE.DATEFIELD} in {@FROMDATE1} to {@TODATE1}
ELSE IF {?PEROD} = "2" THEN {TABLE.DATEFIELD} in {@FROMDATE2} to {@TODATE2}

So, you should be able to do the same by simply replacing my dates with yours, my table.field in the select with yours. You would also need to wrap the if then in ( ), like this.

TABLEB.FIELD = "NA" and
(IF {?PEROD} = "1" THEN {LIMS_USERS.CHANGED_ON} in {@FROMDATE1} to {@TODATE1}
ELSE IF {?PEROD} = "2" THEN {TABLE.DATEFIELD} in {@FROMDATE2} to {@TODATE2})

I cannot tell exactly how yours is setup and therefore cannot tell for sure while it's failing but perhaps the date formulas are not populated before the query is executed.

-- Jason
"It's Just Ones and Zeros

 

[lbass]

You should check the SQL query (database->show SQL query) to see what parts of your selection formula are passing--you want to see all of them included. I think you should remove the nested formulas, and instead use:

{NA_SO_ORDER_CONF_VW.GL_CMP_KEY} = "NA" and
(
(
{?First_or_Second_Run} = 1 and
{NA_SO_ORDER_CONF_VW.SO_HDR_CRTDT} >= Datetime(currentdate-1,time(16,30,01)) and
{NA_SO_ORDER_CONF_VW.SO_HDR_CRTDT} <= datetime(currentdate,time(12,0,0))
) or
(
{?First_or_Second_Run} = 2 and
{NA_SO_ORDER_CONF_VW.SO_HDR_CRTDT} >= Datetime(currentdate,time(12,0,1)) and
{NA_SO_ORDER_CONF_VW.SO_HDR_CRTDT} <= datetime(currentdate,time(16,30,0))
)
)

I removed the date parameter altogether since you seem to want to evaluate yesterday and today's shifts.

-LB