subtracting datetime from discrete parameter

[rrmcguire]

Is it possible to subtract a datetime field from a discrete parameter which is also a datetime.

ex. my datetime field is BILLED_DATE.

I have a formula @age which is {?Pm-AgingDate}- {BILLED_DATE}

?Pm-AgingDate is a discrete datetime parameter field which when the user runs the report should prompt them for a date to enter.

Then take that - BILLED_DATE.

But when the report is ran I receive this: '' is not a valid date and time.

 

[lbass]

So you are working within a subreport that is linked on {?Pm-AgingDate}. I think you should be able to use the formula, so it would be important to observe how the datetimes display. Try placing them in the detail section of the sub, and also check the datatype of each by running your mouse over them and observing the datatype specified in the tooltip.

-LB

 

[rrmcguire]

No, Pm-AgingDate is just a parameter which the user should be able to enter in a discrete value in.

I made another report which just this parameter the @age and BILLED_DATE and it prompted me correctly for the parameter field to enter, so I beieve its probably something else within the report itself.

Also, the datatype didn't matter whether datetime or just date.


thanks

 

[lbass]

{?pm-} indicates a linking parameter. Does it appear in your field explorer->parameter list? And the datatypes are?

-LB

 

[rrmcguire]

yes, it does appear in the field explorer, sorry my mistake the parameter is just Pm-AgingDate

thanks

 

[lbass]

And the datatypes?

-LB

 

[rrmcguire]

Pm-AgingDate is Date and BILLED_DATE is datetime, although it didn't seem to matter if I took the parameter field and BILLED_DATE, created another report it runs correctly regardless of the field types of the paramter and BILLED_DATE

 

[lbass]

Try changing the formula to:

{?Pm-AgingDate}-date({BILLED_DATE})

-LB

 

[rrmcguire]

no still the same message '' is not a valid date and time

 

[lbass]

What is it referring to? WHAT is not a a vlid date and time? What is the exact message? It sounds like you have nulls, so try this:

Create a formula {@billed}:

if isnull({Billed_date}) then
{?pm_agingdate} else
date({Billed_date})

Then change your formula to:

{?pm_agingdate}-{@billed}

This would return 0 if there is no bill date--not sure if this is what you would want to happen in this case.

-LB