Accessing Date field problems 1

[Wayne79]

Using CR8.5, DB2 v7

I am writing reports against a series of table where the dates are defined as DateTime. Here is a sample of the fields data: 2004-11-07-21.00.01.000000

I create a parameter and set it's data type to be date, range values.

In the report record selection criteria I enter
{RPT_CBC_BILLING.PICKUP_BY} = {?Dates}

I get an error saying string is required here, pointing at my parameter.

I've used the formula
cdate(val(mid({RPT_CBC_BILLING.PICKUP_BY},1,4)),val(mid({RPT_CBC_BILLING.PICKUP_BY},6,2)),val(mid({RPT_CBC_BILLING.PICKUP_BY},9,2))) = {?Dates}
with good results...If I am only using it to filter the records to be selected.

However I get a sum cannot be generated when using this formula (probably because the date is being read as a string):
Sum ({RPT_CBC_BILLING.CHARGES}, {RPT_CBC_BILLING.PICKUP_BY}, "daily") +
(
Sum ({RPT_CBC_BILLING.STOPS}, {RPT_CBC_BILLING.PICKUP_BY}, "daily") *
Maximum ({RPT_CBC_BILLING.STOPS_RATE}, {RPT_CBC_BILLING.PICKUP_BY}, "daily")
) +
(
Sum ({RPT_CBC_BILLING.DROP_HOOK}, {RPT_CBC_BILLING.PICKUP_BY}, "daily") *
Maximum ({RPT_CBC_BILLING.DROP_HOOK_RATE}, {RPT_CBC_BILLING.PICKUP_BY}, "daily")
)

I've been told to make sure that File > Report Options > Convert Date-Time Field: is set to Date or Date Time. I've done both with neither working. Currently it is set to Date.

What do I need to do to get CR to treat these fields as dates?

 

[Madawc]

Put the date inside a formula field. It should then be OK.

Try just saying Cdate({your.field}), which might be a simpler option than the formula you're using. If this does not produce a date, try Cdate(Left, {your.field}, 10), which should isolate the date portion.

Madawc Williams (East Anglia, UK)
Windows XP & Crystal 10

 

[Wayne79]

used both CDate({RPT_CBC_BILLING.PICKUP_BY}) and
Cdate(Left({RPT_CBC_BILLING.PICKUP_BY}, 10)).

Neither worked. Still the sum could not be created error.

 

[Madawc]

Did the formula fields appear as dates? Place them on the report, right-click and format field to see what you've got.

If you have dates, then try breaking down the elements of your sum and see which is failing. It might be better to do it by several running totals and a formula field to put them together.

Madawc Williams (East Anglia, UK)
Windows XP & Crystal 10

 

[Wayne79]

CR thinks the fields are strings. I do not get a tab to format it like I do with numbers.

 

[lbass]

Use your initial formula {@date}:

cdate(val(mid({RPT_CBC_BILLING.PICKUP_BY},1,4)),val(mid({RPT_CBC_BILLING.PICKUP_BY},6,2)),val(mid({RPT_CBC_BILLING.PICKUP_BY},9,2)))

Then plug this into your summary formula, as in:

Sum ({RPT_CBC_BILLING.CHARGES}, {@date})+
(
Sum ({RPT_CBC_BILLING.STOPS}, {@date})*
Maximum ({RPT_CBC_BILLING.STOPS_RATE}, {@date})
) +
(
Sum ({RPT_CBC_BILLING.DROP_HOOK}, {@date}) *
Maximum ({RPT_CBC_BILLING.DROP_HOOK_RATE}, {@date})
)

The formula will only work if you have a group on {@date}. You don't need to specify daily in the formula as this is the default.

-LB

 

[Wayne79]

That worked. Thanks for the assistance.