Access 2010-需要用值替换空值 [英] Access 2010 - Need to replace a null with a value

问题描述

我有一个查询，查询两次，计算差值并将值(以秒为单位)放在另一个字段中.然后，我对该字段进行排序.问题是，当其中之一为空时，新字段中的行未填充，因此当我对行进行排序时，它们中的任何内容都不会被推到顶部.

I have a query that looks at two times and calculates the difference and puts the value (in seconds) in another field. I then sort that field. The problem is when one of the times is empty the row in the new field in not populated so when I do the sort the rows that nothing in them are pushed to the top.

我想做的是将null替换为另一个值.类似于9999999.这样，当我执行排序时，现在具有999999的行将放置在排序的底部.

What I would like to do is replace the null with another value. Something like 9999999. That way when I perform the sort the rows that now have 999999 will be place at the bottom of the sort.

这是查询的SQL表达式.

Here is the SQL Expression for the query.

    SELECT FOCFClassic.FirstName, FOCFClassic.LastName, FOCFClassic.[Bib#], FOCFClassic.[2011SDStartTime], FOCFClassic.[2011SDFinishTime], Diff2Dates("ns",[FOCFClassic].[2011SDStartTime],[FOCFClassic].[2011SDFinishTime]) AS 2011SDRunTime, FOCFClassic.SDCategory, FOCFClassic.Team, FOCFClassic.SDRank, DateDiff("s",[2011SDStartTime],[2011SDFinishTime]) AS TotalTime
    FROM FOCFClassic
    WHERE (((Diff2Dates("ns",[FOCFClassic].[2011SDStartTime],[FOCFClassic].[2011SDFinishTime]))<>"") AND ((DateDiff("s",[2011SDStartTime],[2011SDFinishTime])) Is Not Null))
    ORDER BY FOCFClassic.SDRank, DateDiff("s",[2011SDStartTime],[2011SDFinishTime]);

我是这个菜鸟，所以需要我的一只小手.

I'm a noob at this so a little hand holding my be needed.

谢谢前进！ 戈登

推荐答案

您可以使用NZ()函数:如果字段为null，则NZ(MyDate, #1/1/1950#)将返回1950年1月1日，否则返回字段值.您可以使用IIF()和ISNULL()函数或IS NULL条件获得相同的结果.
在性能方面，使用IIF(myDate IS NULL, #1/1/1950#, myDate)应该是最快的.

You can either use the NZ() function: NZ(MyDate, #1/1/1950#) will return jan 1st 1950 if the field is null, the field value otherwise. You can achieve the same result using IIF() and ISNULL() functions or IS NULL condition.
In terms of performance, using IIF(myDate IS NULL, #1/1/1950#, myDate) should be the fastest.

这篇关于Access 2010-需要用值替换空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！