vba 当十进制的字符串值转换为十进制并且只打印空字符串时，Microsoft/SSRS 报告文本框返回 #error
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18408344/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Microsoft/SSRS report textbox returns #error when string value that is decimal is converted to decimal and empty string is just printed
提问by IWriteApps
I have a value that is coming back from a database as string, but it could be a decimaland when it is a decimalI want to display it as a decimaland format it a certain way, if it isn't a decimalthen I just display whatever value comes back.
我有一个作为字符串从数据库返回的值，但它可能是 a decimal，当它是 a 时，decimal我想将它显示为 adecimal并以某种方式对其进行格式化，如果它不是 adecimal那么我只显示任何值回来。
Here's my code:
这是我的代码：
=Iif
(
IsNothing(Fields!Amount.Value) Or Len(Trim(Fields!Amount.Value)) < 1,
Fields!Amount.Value, //have even tried to do CStr(Fields!Amount.Value) in case conversion below makes the report expect decimals for all values
Iif
(
IsNumeric(Fields!Amount.Value),
CDec(Fields!Amount.Value),
Fields!Amount.Value
)
)
The comment above is not part of the code, I just put that here. Anyway, based on the above, all decimals are successfully converted to decimals and display ok, but all those strings that are either empty or hold a non-numeric value show up as #Error.
上面的注释不是代码的一部分，我只是把它放在这里。无论如何，基于上述，所有小数都成功转换为小数并显示正常，但是所有那些为空或包含非数字值的字符串都显示为#Error。
Here's a sample result display:
这是一个示例结果显示：
72.00
95.00
#Error
20.00
What's wrong with this expression? and why couldn't SSRS use c# instead of VB?!!?
这个表达有什么问题？为什么 SSRS 不能使用 c# 而不是 VB？！！？
UPDATE:I know that the problem has to do with the conversion and not the logic to check whether the value is nothing, less than 1 character, or a numeric, because the following works:
更新：我知道问题与转换有关，而不是与检查值是否为空、小于 1 个字符或数字的逻辑有关，因为以下方法有效：
=Iif
(
IsNothing(Fields!Amount.Value) Or Len(Trim(Fields!Amount.Value)) < 1,
"is nothing or less than 1",
Iif
(
IsNumeric(Fields!Amount.Value),
"is numeric",
"is not numeric"
)
)
this will correctly display:
这将正确显示：
is numeric
is numeric
is nothing or less than 1
is numeric
回答by IvanH
Iifis a function and so all arguments are evaluated before the function is called. So there is no use in using Iif to prevent error.
Iif是一个函数，因此在调用函数之前评估所有参数。所以使用 Iif 来防止错误是没有用的。
I suppose you need a User defined function (Writing Custom functions in SSRS)
我想你需要一个用户定义的函数（在 SSRS 中编写自定义函数）
So instead Iif(IsNumeric(Fields!Amount.Value),...)
所以与其 Iif(IsNumeric(Fields!Amount.Value),...)
define a function
定义一个函数
Function DecimalIfPossible(Value as string) As Object
If IsNumeric(Fields!Amount.Value) then
Return CDec(Fields!Amount.Value)
else
Return Fields!Amount.Value
End if
End Function
and call it DecimalIfPossible(Fields!Amount.Value).
并调用它DecimalIfPossible(Fields!Amount.Value)。
回答by Anup Agrawal
Why not convert the decimal back to string
为什么不将十进制转换回字符串
IIF
(
IsNumeric(Fields!Amount.Value),
CStr(CDec(Fields!Amount.Value)),
Fields!Amount.Value
)
Based on your logic you are looking for empty or Null string, so handle it in SQL. Convert it to NULL if it is non numeric something like
根据您的逻辑，您正在寻找空字符串或 Null 字符串，因此请在 SQL 中处理它。如果它不是数字，则将其转换为 NULL
CASE WHEN ISNUMERIC(Amount) = 1 THEN Amount ELSE NULL END As Amount
or use NULLIF
或使用 NULLIF
