Problem with Num function

[#1]

Hi Friends,

Facing an issue with the number formatting, when I used the Num function on  a negative value as Num(InVoiceBudget_Amt_TY,'#,##0.0#;-#,##0.0#','.',',')  it gives a positive value as 11088128.1 which is actually -6421321.059525 at back end

If anybody has faced this issue earlier, could you share the details of possible solution?

Thanks,

Raj

[rustyfishbones]

Hi Rajesh

try this removing the ,'.',','

Num(InVoiceBudget_Amt_TY,'#,##0.0#;-#,##0.0#')

[#3]

Hi,

Try this

= num(-6421321.059525 ,'#,##0.0')

It will return -6,421,321.1,  No matter the number is positive or negative... Use InVoiceBudget_Amt_TY instead of -6421321.059525

Thanks,

Selvakuamr

[#4]

Hi Alan,

Thanks for your time and reply, I see removing the decimal and thousand separator is not an option for me, because my local environment has '.' as decimal separator and ',' as thousand separator, where as the QA and Prod environments uses them other way around, as the servers located in Europe.

I have tried over coming this by setting the system variables, but unfortunately that doesn't seems to serve the purpose, or may be I am missing some thing while setting the system variables, I used the below statements for setting the system variables

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='¥#,##0.00;¥-#,##0.00';

SET NumberFormat='#,##0.0#;-#,##0.0#';

So my whole problem is about using the consistent decimal and thousand separators throughout the environments, I thought I could force it by using Num function, since setting the system variables didn't serve the purpose.

Does this strike any possible solution for you?

my sincere thanks for your time...

Raj

[Anonymous]

I checked  your formula only and I got the correct result,

=num(-6421321.059525 ,'#,##0.0#;-#,##0.0####','.',',')

Can you post the sample app showing error

[er_mohit]

if you want as same as your field then use text function i.e

text()

or you can use num function also like this

Num(if(len(InVoiceBudget_Amt_TY)>0,InVoiceBudget_Amt_TY),'#,##0.0#;-#,##0.0#')

hope it helps

[#7]

Thanks for your valuable suggestions friends,

I found the problem is at data source - Ms Access selection query level itself, from the Access SQL the data returned is already formatted before it reaches the QV Load statement according to the regional settings, resolved this by resetting the Region & Language settings.

Best Regards,

Rajesh