开发者

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.

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.

Here is the SQL Expression for the query.

    SELECT FOCFClassic.FirstName, FOCFClassic.LastName, FOCFClassic.[Bib#], FOCFClassic.[2011SDStartTime], FOCFClassic.开发者_如何学C[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.

Thanks is advance! Gordon


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜