Date Parameter 1

[CrystalVis]

Hello All,

I have the following data in the table:
Date Closed
08/17/2002
08/22/2002
08/22/2002
08/22/2002

Here is my record selection statement
{tbl.DateClosed} <= {?Date}

when I enter 8/22/2002, only one record return (8/17/2002). However, when I enter 8/23/2002, all four records return. Is this mean the date parameter is not inclusive? Your help/input is greatly appreciated. I am using CR8.5 with Oracle backend.
TIA

 

[CrystalVis]

Does this mean Crystal ignores the = operator?

 

[rosemaryl]

Dear CrystalVis,

When you refresh the report at the parameter prompt, do you have include value checked?

If not, that would be the issue.

HTH,

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[kenhamady]

Or it could be that there is a time that isn't being accounted for correctly in the SQL statement. Is your report converting DateTimes to Dates?
What is the SQL WHERE clause being generated for each example (Database - Show SQL). Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[lyanch]

It depends. Your &quot;date&quot; field is really a &quot;datetime&quot; field in Oracle. After entering your paramter, go look at the SQL. Normally, in 8.5, when you put {datetimefield} <= {date} crystal will write the SQL as {datetimefield} < {date +1 00:00:00} .. in other words it changes your date parameter to a datetime parameter and sets it to be just past midnight (thus including the entire date). Check that and see what happends.

Lisa

 

[CrystalVis]

Thanks you all for respond. You are right, the DateClosed field is a date time field in Oracle, but I already set Convert Date Time Field TO DATE. The parameter type is DATE. I don't know why it is not working???

This is my where clause
Table.DATECLOSED <= {ts '2002-08-18 00:00:00.00'}

Rosemaryl,
I think the option you are referring to only available when you use Range Values. My parameter is Discrete Value.

 

[synapsevampire]

Crystal sometimes does bad, bad things with pass through SQL.

Try creating a formula to use in the record selection criteria as in:

@MyDateFormula
datetime(year({?Date}),month({?Date}),day({?Date}),23,59,59)

In the record selection criteria:

Table.DATECLOSED <= @MyDateFormula

It should pass the SQL and be accurate.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[CrystalVis]

synapsevampire,

That did it!!! Thank you very much for your help. I guess now my record selection process will not be pushed down to the server since I use a formula. Again, thanks all for your inputs.
Bryan

 

[synapsevampire]

It will indeed be pushed down to the server, just don't use variables!

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kenhamady]

CrystalVis,

You can check to see if the SQL WHERE clause has the date criteria by looking in:

Database - Show SQL Query.

Let us know what you see. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com