Stu, answers "inline":
1) Does ExecuteSQL strip the leading zero, and if so, would having it there make any difference?
the ExecuteSQL() function returns TEXT. You will not see leading 0's in the "decimal numbers".
2) Has anybody else used the CF I'm working with and found there are similar results with numbers under 1.00?
there is a link to the author of the CF on the page you referenced. have you tried to contact Daniele? and/or post a comment on the page
The custom function is made using "word" functions. (RightWords, WordCount, MiddleWords and so on.) And I think that "." is a "Word delimiter" and therefore disappear.
The custom function should be remade using "Values" functions instead. (RightValue, ValueCount, MiddleValue and so on.)
But that is not all. The CF is also using the Truncate function, which would remove all decimals and in your case return zero.
With best regards Magnus Fransson.
One thing I've managed to do here, based on something you showed me previously, is to add something to the ExecuteSQL just after the 'select':
Select ''||(FeeValue)from Fees where
Area = ? and
Category = ? and
Description = ? and
Service = ? and
cYearEndedIn = ?
Order by FeeValue ASC
I'm using SQL for other numeric work throughout my project (somehow!) and it seems to be working with this setup as above. This will only work when complimented with 'proper' data, ie, instead of .74 showing in a field, it needs to be 0.74 etc.
Previously I've used CAST varchar on MSSQL - I can't see there's anything equivalent to actually change data types??
yes! You are concatenating a NULL constant with your field value. The results are text regardless. But it's a handy trick!
There is a CAST() that can be used with ExecuteSQL() too, but you have to be very careful using it.
Truncate() is used for index (position of number), not number value. It can be replaced with Int().
values = RightValues ( valueList; Int ( ( ( ValueCount ( valueList ) - 1) / 2 ) + 1 ) );
count = ValueCount ( values ) ;
odd = GetValue ( values ; Int ( count / 2 ) +1 );
even = GetValue ( values ; Int ( count / 2 ) )
Mod ( count ; 2 ) ; odd ; ( even + odd ) / 2
You are right. After my previous post I took a closer look at the CF. And the Truncate() function is used for "positions" only. They can either remain or be replaced with either Int() or Floor().
My claim that the dot (".") should be a "word delimiter" is actually more complex then just that. It seems as, when next to another delimiter (like "new line" for example) it is removed. But when between two numbers it is considered to be a part of that word. That is why Stu can make the CF work by concatenating zeros ("0") infront of the values.
But it would be more "right" and reliable to use "Value" functions, like in your example. (Maybe even in conjunction with "GetAsNumber()" function.) That would make the concatenation with zeros redundant and unnecessary.
With best regards Magnus Fransson.
Fran55on, User19752, Beverly
Thanks for the help with this one. I was trying to redo the CF yesterday to set as values, not words. The inclusion of the INT command here to replace Truncate has managed to fix a couple of issues:
1) I can use my original FeeValue field, even though the leading zeros may be stripped.
2) I concatenate a null character at the start of the ExectuteSQL to capture the values as I need them
3) The new 'numeric' CF works perfectly and is now returning the values I expect (ie, matching those I calculate away from FM using traditional methods!)
Thanks everyone for help from you all!