即使值为null，如何显示记录 [英] How to show records even if the value is null

问题描述

我是Access的初学者，我在XP上使用2007版本。我创建了一个数据库，可以跟踪我工作的员工工作时间。一切都运转良好，直到我们最近雇用了一个新人。我有一个子查询，用于检查某人在指定日期范围之间的加班时间，而新员工根本没有出现。我在想它是因为没有输入小时信息，因为他是新的，所以查询返回一个空值或其他东西。我试图使用一个iif语句，如果它为null，则返回零，但是没有用;但是，我可能没有正确的语法或其他东西。无论如何，这里是查询：

展开 | 选择 | Wrap | 行号

解决方案

欢迎到字节！


在你的桌子上使用左连接做得好 - 这是一个好的开始。左连接意味着将包含所有员工记录，具体取决于您使用的WHERE子句。


您需要确保引用加入正确方面的字段，但是 - 请将员工表ID替换为当前列出的加班时间员工ID，例。您还需要更改WHERE子句和Group By子句 - 现在的WHERE子句将阻止您查看任何没有加班时间的记录，因为WHERE仅选择某些加班日期之间的记录。


下面列出了要修改的修订版本。

展开 | 选择 | 换行 | 行号

< blockquote>斯图尔特，


感谢您的回复。你说的完全有道理。我做了改进但结果没有变化。在设计视图中，我注意到它说< >假"根据标准。我将其改为真，然后新员工出现了，但它也从一开始就累计了其他人的加班时间。还有其他想法吗？


感谢您的帮助！我很感激。

嗨。您可以像现在一样发布查询的实际SQL吗？我不知道''<> false''标准正在应用，因为它在原始SQL中没有出现，因此可能是查询中的SQL不是我期望的那样。一旦审查完毕，我会回复一些有关克服当前问题的建议。


-Stewart

I''m a beginner with Access and am using the 2007 version on XP. I''ve created a database that keeps track of employee hours where I work. Everything has worked fine until we recently hired someone new. I have a subquery that checks for the amount of overtime hours someone has between a specified date range, and the new employee doesn''t show up at all. I''m thinking it''s because no hours information has been entered in because he was new, so the query is returning a null value or something. I''ve tried to use an iif statement so that if it''s null it returns a zero but that didn''t work; however, I may have not had proper syntax or something. Anyway, here''s the query:

Expand|Select|Wrap|Line Numbers

解决方案

Hi, and Welcome to Bytes!

Well done for using a left-join with your tables - that''s a good start. The left join means that all of the employee records will be included, subject to whatever WHERE clause you are using.

You will need to make sure you are referring to the fields from the correct side of the join, however - so substitute the employee table ID for the overtime hours employee ID currently listed, for example. You will also need to alter the WHERE clause and the Group By clause - the WHERE clause as it stands will prevent you from seeing any of the records which have no overtime hours, because the WHERE selects only those which are between certain overtime dates.

A revised version to try is listed below.

Expand|Select|Wrap|Line Numbers

Stewart,

Thanks for the reply. What you''re saying totally makes sense. I made the improvements though and there is no change to the result. In the design view I noticed that it said "< > False" under criteria. I changed that to true and then the new employee showed up but it also added up everyone else''s overtime hours from the beginning. Any other ideas?

Thanks for the help! I appreciate it.

Hi. Could you post the actual SQL for your query as it is now, please? I don''t know what field the ''<> false'' criterion was being applied to as it was not present in the original SQL posted, so it may be that the SQL in your query is not quite what I expect it to be. Once this is reviewed I''ll post back with suggestions for overcoming the current problem.

-Stewart

这篇关于即使值为null，如何显示记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！