Clarion dates in Crystal parameters

[Sontec]

We are trying to extract data from a SQL database that has been written by Clarion. The clarion date is in a number format (76431) and we have a formula to convert it for printing on the crystal report. However we need to use a date parameter to select certain data from the database and this parameter presents the data to us in the Clarion format (eg 76431). How can we convert that date so it is displayed to the user in the date parameter in a normal date format.

The formula we are using to convert the dates is:

If {Spares.DATE_RECD} > 0 Then
Date (1800,12 ,28 ) + {Spares.DATE_RECD}

 

[Reebo99]

You will need to use the DATEADD function. Off the top of my head, I think the syntax is:

If {Spares.DATE_RECD} > 0 Then dateadd("d",{Spares.DATE_RECD},Date (1800,12 ,28 ))

The above formula assumes that {Spares.DATE_RECD} is the number of days after 28/12/1800. Juan
Scotland (currently)

 

[Sontec]

But how do we get the date into a parameter? Maybe i'm missing something...

As an example, we want to pull out a report of all spares receivd between 1.1.2003 and 20.1.2003, but because there are no crystal recognised dates in any of the tables, we haven't been able to set the range defaults.

Does that make sense?

Thanks for replying so quickly.

 

[Reebo99]

You base your selection on the formula I've highlighted above.
let's assume {Spares.DATE_RECD} = 65000

e.g formula name DateConversion

If {Spares.DATE_RECD} > 0 Then dateadd("d",{Spares.DATE_RECD},Date (1800,12 ,28 ))

give the formula field a value of 15/12/1978 00:00:00

When specifying your record selection formula :
{@DateConversion} in DateTime (1978, 12, 10, 00, 00, 00) to DateTime (1978, 12, 20, 00, 00, 00)

will show all records where the value of DateConversion is between 10/12/1978 and 20/12/1978.
Juan
Scotland (currently)

 

[Sontec]

OK, I understand that much, but the report needs to prompt users for the date range they need each time they run the report...we don't want the report to run the same date range each time. I need to add a filter that selects the date received from a parameter where the user has selected the from/to dates.

Thanks again for your help...

 

[Reebo99]

Sorry, I can't be of any further help with regard to prompting for parameters. Hope you find what you need.

Have a Nice Day! Juan
Scotland (currently)

 

[kenhamady]

I think you want to prompt for a normal date range using a DATE parameter. Then write a selection formula that says:

{ClarionDateFormula) in {?DateRangePrompt} Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[Sontec]

Thanks for all your help...

It turned out to be quite simple...all I did was setup a normal date range paramater without default values and made the filter refer back to the formula using the 'is equal to' option...i was always looking at 'is in the period' cos it was a date range.