cancel
Showing results for 
Search instead for 
Did you mean: 

Expression Box Calculation Error

Former Member
0 Kudos

I have displayed the output of a query in a table. One of the columns in

the table shows 'Creation Date'. So I add an 'expression box' to the

table and enter the following expression in the Formatting Custom tab...

NSTR(DSUB(@Creation_Date,NOW(),'D'),'M')

The objective is to display the number of days lapsed since the record

was created on each row of the table. The system keeps returning -14018

or some other garbage on each row.

Please help.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

the expression commands like DVAL(), DSUB(), etc. require the dates in a specific format, which does not have to be similar to the format represented in portal or given by queries and so on.

You should experiment with double translation, i.e.

DVAL(DSTR(@DATUM1,'MM.DD.YYYY'))

Convert date to string and back to date.

This was the only way I could get date-issues get to work.

Kind Regards,

Benni

Former Member
0 Kudos

The problem is still there. The output in the expression box is always a constant value, although the Creation Date is constantly changing in each row. Latest attempt was using the following expression

DSUB(DVAL(DSTR(@Creation_Date,'MM.DD.YYYY')),DVAL(DSTR(NOW(),'MM.DD.YYYY')),"Y") returns -38.4

Thanks both of you for the help.

Regards,

Adeel Hashmi

Answers (2)

Answers (2)

Former Member
0 Kudos

The latest screenshot on this issue is attached. After adding DVAL and using DSTR to convert to the desired date format. The results are nonsensical and useless. System somehow is calculating negative values for dates beyond the current calender month.

In screenshot, STR1 is the Creation Date after some formatting and STR2 is the current date. ff is the column showing the difference in days between the two dates using the following command

DSUB(DVAL(@STR2),DVAL(@STR1),'D')

http://www.geocities.com/fishal123/VC_error2.JPG

Regards,

Adeel Hashmi

Former Member
0 Kudos

Hi,

why are you using NSTR funtion.Instead of this use DSUB(NOW(),@CREATION_DATE,'D') in the expresiion filed to get the total no of elapsed days.

Just try it and let me know

Regards,

Govindu

Former Member
0 Kudos

Thanks Govindu. I tried that but the result is still the same. The data type of the 'Creation Date' field is Date, so I cant fathom why it doesnt work.

http://www.geocities.com/fishal123/vc_error.jpg

Former Member
0 Kudos

Hi ,

On whcih SP u are working.

I think this is happening due to date fromate:

Creation_date:2008.05.08 i.e yyyymmdd

and if now() has 20.05.2008 i.e ddmmyyyy

then DSUB () may result invalid values.

Regards,

Govindu