[RESOLVED] Equivalent of Nz() Function

[Shaggy Hiker]

I have query that includes this in the SELECT statement (as one of many fields):

IIF(STRM_Survey.flgNoSurvey = 0,Nz((SELECT SUM(CountNew) FROM STRM_Redds WHERE STRM_Redds.SurveyID = STRM_Survey.SurveyID),0),Null)  AS NewRedds

That works in Access, but not in ADO.NET, because Nz is not recognized in ADO.NET. In SQL Server, I would use IsNull rather than Nz, but IsNull is a recognized function in Access, and it doesn't do the same thing as it does in SQL Server.

I realize that I could write this as an utterly ugly IIF within IIF, but it seems like I would have to write it like this:

IIF(STRM_Survey.flgNoSurvey = 0,IIF(ISNull(SELECT SUM(CountNew) FROM STRM_Redds WHERE STRM_Redds.SurveyID = STRM_Survey.SurveyID),0),SELECT SUM(CountNew) FROM STRM_Redds WHERE STRM_Redds.SurveyID = STRM_Survey.SurveyID)  AS NewRedds

which seems like a massive waste of time, as it runs a sub-query twice. Therefore, I'm looking for a replacement for Nz that will work in ADO.NET. Is there such a thing?

I do have an alternative, in this case, but it is probably worse than the double sub-query.