Date strings 1

[peterRed]

I am using Vr8.5
I am trying to amend a report previously written by someone else to show a particular date range.

The problem begins when the parameter asks for a date range ie From Date and To Date. The parameter has set the date value as a string. If I change the field value to date and set the default values, the formula @fromdate and @todate shows an error saying a string is required. The formula is

if not isnull({?fromdate}) then (
numberVar year1 := Val (left({?fromdate},4));
numberVar month1 :=Val (mid({?fromdate},5,2));
numberVar day1 := Val (right({?fromdate},2));
Date(year1,month1,day1) )
else Date(0,0,0)

If I leave the parameter settings as a string and enter the date ranges as from 20030501 to 20030531 (month of May) the report displays the entire history which covers a 6 month period.
how can i display the month of May only?
thank you for your help

 

[IanWaterman]

Peter

Looked like whoever wrote the original liked to make things difficult for them selves.

Change parameters to date types and enter a default date, this eliminates the checking for nulls.

Then just use use the parameters in your selection criteria ie

{datefield} >= {?Fromdate} and
{datefield} >= {?Enddate}

Alternatively you could use a single date parameter but make it a range type. Then selection formula becomes

{datefield} >= minimum({?DateRange}) and
{datefield} >= maximum({?DateRange})

Ian Waterman
UK Crystal Consultant

 

[Madawc]

Crystal 8.5 allows even simpler ranges, in the format
{Your.Value} in From_Val to To_Val

Madawc Williams
East Anglia, Great Britain

 

[peterRed]

Thank you for your replies. I understand your instructions to change the parameters to date types.

I assume you mean to inset {datefield} >= {?Fromdate} and
{datefield} >= {?Enddate} into the formula instead to the current @todate and @fordate formula. In doing that CR indicates there is no error when a check is run on the formula.

When I the Refresh the report the following formula drops our with the message “A Boolean is required here”

(IsNull({?clientid}) or (trim({?clientid}) = '') or ({cv_fare_saving.client_code} = {?clientid}))
and
(IsNull({?deborid}) or trim({?deborid}) = '' or {cv_fare_saving.debtor_id} = {?deborid})
and
(IsNull({?servicetype}) or trim({?servicetype}) = '' or {?servicetype} = 'A' or ({cv_fare_saving.service_type} = {?servicetype}))
and
(if Trim({?datetype}) = 'Q' then (({@fromdatecalc} = Date(0,0,0) or {cv_fare_saving.quote_date} >= {@fromdatecalc}) and
({@todatecalc} = Date(0,0,0) or {cv_fare_saving.quote_date} <= {@todatecalc}))
else if Trim({?datetype}) = 'T' then ({@fromdatecalc} = Date(0,0,0) or {cv_fare_saving.trip_departure_date} >= {@fromdatecalc} and
({@todatecalc} = Date(0,0,0) or {cv_fare_saving.trip_departure_date} <= {@todatecalc}))
else True)

Am I following your instructions correctly? Thank you for your assistance and help
Peter

 

[IanWaterman]

You did n't mention that the parameter was being used in a formula, looks like it is being used in formulas {@fromdatecalc} and {@todatecalc}.

You will need to see what is being returned from these formulas and then modify the above formula to cope with a date instead of a number.

For example you may need to change the last line
or {cv_fare_saving.trip_departure_date} <= {@todatecalc}))
else True)

to

or todate({cv_fare_saving.trip_departure_date}) <= {@todatecalc}))
else True)

 

[peterRed]

Ian,
After inserting the change to the formula a further error occurred in another formula. As there are other sub reports attached to the main report I ended up bypassing/ignoring the formulas which were dropping out, I then created my own parameters. Everything worked out in the end. Thanks for your help without it I would probably still be clashing with formulas.
Peter