在查询中无效使用null [英] Invalid use of a null in query

问题描述

大家好，


我试图设置''应收账款''老化查询，但是，当我试图运行时，我不断收到此错误消息。这是sql（查询）：

展开 | 选择 | Wrap | 行号

解决方案

大家好，


我试图设置''应收账款''老化查询，但是，当我试图运行时，我不断收到此错误消息。这是sql（查询）：

展开 | 选择 | Wrap | 行号

Rosie。


看起来Clng参数的计算结果为Null。请注意，根据三态逻辑，只要表达式中的一个字段为Null，整个表达式也会计算为Null。


在您的情况下，如果以下字段之一

[数量]

[UnitPrice]

[折扣]

为Null，Clng参数的计算结果为Null，这会引发错误。


查询的其余部分似乎是Null证明。

非常感谢您的评价......这些字段来自4个表的查询中的一个表（OrderDetails）...这样做意味着它没有在表格中找到任何数据？...由于订单详细信息充满了查询本身存在缺陷的信息，这会很奇怪？它是内连接，当两个表中的任何数据都没有实现时，查询产生一个Null吗？


感谢您对此问题的进一步了解。 />
Rosie

非常感谢您的评价......这些字段来自其中一个表格（OrderDetails）在4个表的查询中...这是否意味着它没有在表中找到任何数据？...由于订单详细信息充满了信息，查询本身存在缺陷，这会很奇怪？它是内连接，当两个表中的任何数据都没有实现时，查询产生一个Null吗？


感谢您对此问题的进一步了解。 />
Rosie

如果你有（可能总是如此）与价格相关的数量，你的函数的这两部分将返回值他们应该这样。但是，如果您的折扣字段为空（可能也是大部分时间），则您的函数将返回空值，因为折扣已经过了空。


最好修复此问题的方法是进入包含Discount字段的表设计，并将其默认设置为0.这将确保您永远不会有空值传递给计算。

另一种方式是使用Nz（），但是如果你最终要将其升级为与任何其他编程语言进行交互，那么Nz（）就不会工作。


另一种方法是通过一个iif语句，但同样，这会解决比应该处理的问题高几层的问题。我认为默认值为0是最适合你的情况。


另一个问题，你将它乘以100，然后立即将结果除以100 ......这是故意的？如果我忽略了某些东西，请原谅我，但这不会带来同样的价值吗？从本质上讲，你是这样做的，对吧？ （（（3 * 5）*（1-.0125））* 100）/100 = 14.8125，没有* 100/100你会得到相同的结果吗？


问候，

Scott

Hi all,

I''ve trying to set up a ''receivables'' aging query, however, I keep getting this error message when I attempt to run in. Here is the sql (query):

Expand|Select|Wrap|Line Numbers

解决方案

Hi all,

I''ve trying to set up a ''receivables'' aging query, however, I keep getting this error message when I attempt to run in. Here is the sql (query):

Expand|Select|Wrap|Line Numbers

Hi, Rosie.

It looks like Clng argument evaluates to Null. Be aware that according to tristate logic whenever one field in expression is Null, the whole expression evaluates to Null too.

In your case if one of the following fields
[Quantity]
[UnitPrice]
[Discount]
is Null, the Clng argument evaluates to Null and this raises the error.

The rest of the query seems to be Null proof.

Thank you so much for your evaluation.....these fields come from one of the tables (OrderDetails) in the query of 4 tables...does this mean it isn''t finding any data in the tables?..Which would be weird since the Order Details are full of information of is it the query itself that is flawed? Is it the inner join, that when any of the data from both tables, isn''t fulfilled, the query produces a Null?

thanks for additional insight into this issue.
Rosie

Thank you so much for your evaluation.....these fields come from one of the tables (OrderDetails) in the query of 4 tables...does this mean it isn''t finding any data in the tables?..Which would be weird since the Order Details are full of information of is it the query itself that is flawed? Is it the inner join, that when any of the data from both tables, isn''t fulfilled, the query produces a Null?

thanks for additional insight into this issue.
Rosie

If you have (which will likely be true always) a quantity associated with a price, those two parts of your function will return values like they should. However, if your Discount field is empty (which likely it also will be most of the time), your function will return a null value, since Discount has passed a null.

The best way to fix this is to go into your table design that holds the Discount field, and set it''s default to 0. This will ensure that you never have a null value to pass to the calculation.

Another way is with Nz(), but if you are going to eventually upsize this to interact with any other programming language, Nz() won''t work.

Another way is through an iif statement, but again, this takes care of the problem several layers higher than it should be taken care of. I think default value to 0 is the best way to go with your situation.

Another question, you have it multiplying by 100, then immediately dividing the result by 100... Is this intentional? Forgive me if I''m overlooking something, but doesn''t this just return the same value? In essence you are doing this, right? (((3*5)*(1-.0125))*100)/100=14.8125, you get the same result without the *100/100?

Regards,
Scott

这篇关于在查询中无效使用null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！