on 02-22-2007 5:13 PM
Hello all,
I want to use dynamic names for colums in one of my tables. The names should be determined based on values entered in the drop down box of fiscal year. I have used the following formula:
"ACT " & @fiscal_year
This is working fine, f.e. if I select 2006, the column name shows ACT 2006.
However I also have 1 column that should display the value of the previous year. Thus if I select 2006, it should show ACT 2005. However I cannot do any mathematics with fiscal year because it is a text field.
How can I adopt the formula to this requirement?
Thanks in advance,
Ralph
hi again ralph,
you have several possibilities, all of them a somehow wiered concatenation of text, numeric or date formating expressions.
a simple way to do that would be to pass the text into a numeric value using NVAL(text), the do a substraction of one (from 2006 to 2005) and than pass it back to text using NSTR(n, mask), <use "" for mask>.
another way would be to pick the year as dateformat, do a date-add using DADD() with negativ addition (i.e. -1, "YY") for decreasing one year and then passing the date into a text-value.
regards,
Martin
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Ralph,
from your reply I take it that you start with a text-field rather than a date-field. Makes things a lot easier.
the problem you encountered comes from the portal setting for numbers (including the thousand separator. Just add an < "normal" > instead of the < "" > for the mask in the NSTR() and the display of the year will be fine. Im adding the whole sequence of the expression, with @date being the field containing the original year as text: nstr(nval(@date) - 1 ,"normal")
have fun,
Martin
Message was edited by:
Martin Wolpers
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.