Nulls, zeros, NZ - haaalp

<i><b>Originally posted by : Nomit (h.babraa@lse.ac.uk)</b></i><br /><br />Hello<br /><br />I have SQL that returns the sum of a field. When there are no entries it returns a null. In access you can use NZ (NVL in Oracle) to force a zero to be returned. How do you do this is with ASP? I get a message : NZ is considered undefined<br /><br />Cheers<br /><br />

<i><b>Originally posted by : Jerry Scannell (JScannell1@home.com)</b></i><br /><br /><br /><br />------------<br />Nomit at 6/20/2001 5:46:10 AM<br /><br /><br />Hello<br /><br />I have SQL that returns the sum of a field. When there are no entries it returns a null. In access you can use NZ (NVL in Oracle) to force a zero to be returned. How do you do this is with ASP? I get a message : NZ is considered undefined<br /><br />Cheers<br /><br /><br />I have been going around in circles for months with the Null, Empty, Nothing quagmire that surrounds ASP. It seems the problem centers around the scripting truism that data doesn't have to have a specific data type. As such, empty fields are Null when you would hope they would be 0. I have found a solution that sort of works for most instances. Here it is:<br /><br />In the following examples, objValue is a variable returned from a select statement.<br /><br />1. If you wish to verify a numeric value do the following. The assumption is that there souuld be a numeric value (even 0). Therefore, if the length of the trimmed variable is > 0 then it should be a number and IsNumeris() with perform the testing. Otherwise it is a Null (or empty or whatever ASP wants to call it) so I return false.<br /><br />Function IsNumber ( objValue )<br /> Dim Local_String<br /> Local_String = Trim ( objValue )<br /> if len ( Local_String ) > 0 then<br /> IsNumber = IsNumeric ( Local_String )<br /> else<br /> IsNumber = False<br /> end if<br />end Function<br /><br />And as an extra data validation question because I know you will run across the problem with empty or null date fields. The date manipulation functions (e.g. datediff() ) will crash if you pass it an empty date field. So do this:<br /><br /> if IsDate ( objDATEVAL ) then<br /> DaysSinceLast = DateDiff ( "d", objDATEVAL, Date )<br /> else<br /> DaysSinceLast = 0<br /> end if<br /><br /><br /><br />