formula help with date problem

[Tailgun]

here is my formula
{tblClaim.DateClosed} in {?StartDate} to {?EndDate} and
not isnull({tblClaim.DateClosed})

This is a SQL database. When I run a report with something like a StartDate 11/1/2002 EndDate 11/30/2002 It will not show anything closed on 11/30/2002 if I use 11/1/2002 and 12/1/2002 then it will show the 11/30/2002 information. Can anyone tell me what I need to do for the report to work correctly so that the user will see everything closed from 11/1/2002 11/30/2002 when he inserts those dates in the paramaters ?

BTW I'm using Crystal Reports 8.5 Developer edition inside VB6.

Thanks for any help.

 

[pgtek]

Hi
Try this
your Parameter ?StartDate leave it blank no link to anything so the user can fill it in
same thing for the ?EndDate

Now go yo do the select expert select field closedate is between
?StartDate
and
?EndDate
See id this works

PG

 

[Naith]

You're probably looking at a DateTime field, which you're defaulting to 12am. Any 30/11/2002 records with a time after 12am - which is probably all of them, if the time portion is utilised in the database - will not be returned.

If you are looking at DateTime fields, but would prefer to ignore the time part for reporting, then convert the DateTime to Date.

Naith

 

[synapsevampire]

If this is the record selection criteria, you have options to correct this:

If you aren't using the time part of the datetime database field in your report, and you're using a DATE type of parameter (not datetime), select File->Report Options->Convert Date-time field-> TO DATE

If you need to show time in your report, then as Naith suggested, convert the {?StartDate} and {?EndDate} fields to include the time in separate formulas , and reference the formuals in the record selection criteria.

The following is an example of a Enddate formula constructed from a {?Enddate} parameter of type DATE (not datetime):

DateTime ({?EndDate}, CTime("23:59:59&quot)

Then the record selection might be:

not (isnull({tblClaim.DateClosed}))
and
(
{tblClaim.DateClosed} >= {@StartDate}
and
{tblClaim.DateClosed} <= {@EndDate}
)

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dgillz]

If there is a possibility of a null value, always test for nulls first. SV's formula should work wonderfully. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[Tailgun]

Thanks to you all for your help. I think I finally found that this works for me.

{tblClaim.DateClosed} >= {?StartDate} and {tblClaim.DateClosed} <= {?EndDate}

but I will keep a file of your suggestions just in case this proves to cause other problems.

Again Thanks alot I really appreciate all the help I get in this forum.

 

[synapsevampire]

Makes sense, Tailgun - I never use the IN statement, in SQL or CR.

CR does curious things with the IN statement...

But check the SQL Query to be sure that it's creating proper times.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dgillz]

SV-

What does CR do with an IN statement that doesn't work? This is a new one on me. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[Naith]

IN in CR does work. It just doesn't tend to translate to the SQL which is passed as 'IN', but rather as a series of = and OR statements, or >=,<= where IN is used in conjunction with dates.

I tend to find the interpretation of the IN statement in CR is acceptable, but I think this is what SynapseVampire is referring to.

Naith