Replace no value with 0

[sillyr]

Hi I would like to add into the SQL statement that when there was no value selected put in a 0. I looked at another example on the forum that said to use this Nz([Žurnalas].[Debetas], 0)

but I didn't have any luck. The field that I would like to have a 0 in can also be selected by the user to have a value in it.


SELECT Sum([LU Discarded pounds estimated Lookup].[Estimated Weight]) AS [Estimated Weight3270]
FROM [LU Discarded pounds estimated Lookup] INNER JOIN [Discard Species Haul Log] ON [LU Discarded pounds estimated Lookup].[Estimated Bin Weight] = [Discard Species Haul Log].[Estimated Weight3270];

Thanks

[DonRayner]

Hi I would like to add into the SQL statement that when there was no value selected put in a 0. I looked at another example on the forum that said to use this Nz([Žurnalas].[Debetas], 0)

but I didn't have any luck. The field that I would like to have a 0 in can also be selected by the user to have a value in it.


SELECT Sum([LU Discarded pounds estimated Lookup].[Estimated Weight]) AS [Estimated Weight3270]
FROM [LU Discarded pounds estimated Lookup] INNER JOIN [Discard Species Haul Log] ON [LU Discarded pounds estimated Lookup].[Estimated Bin Weight] = [Discard Species Haul Log].[Estimated Weight3270];

Thanks

The NZ() function is the one that you want. It will return the value that you specify if the value of the recordsource field is null. If it's anything but null then it will return the actual value. How did you try to impliment it into your code? I would have tried it like this.

SELECT Sum(Nz([LU Discarded pounds estimated Lookup].[Estimated Weight])) AS [Estimated Weight3270]
FROM [LU Discarded pounds estimated Lookup] INNER JOIN [Discard Species Haul Log] ON [LU Discarded pounds estimated Lookup].[Estimated Bin Weight] = [Discard Species Haul Log].[Estimated Weight3270];

[Stewart Ross]

Hi. As Don says, Nz is the function you need. You will find extensive help available on such functions if you use the Visual Basic Editor (open any existing code module from the Modules tab, or create a new one to start the editor). Within the editor if you type Nz into the help box you will get a list of matching entries, from which you can select the full help info on the function. Its usage is

Nz(item_to_test , value_to_return )

and I guess you were not clear how to use it in your circumstances.

It was a part of my reply to a previous poster that you must have seen where I recommended the use of Nz in place of using an in-line IIF/IsNull combination (I remember the non-english field name inside the Nz which you quote).

-Stewart

[sillyr]

Thanks for your help. It worked. Sorry I didn't include where I had tried it, but I was really frustrated at the time. Thanks again

[sillyr]

Ok- I lied a little. It works when I have a single SQL statement like in my example, but when I add in more statements it is not working. If there is no value in a record then it will not sum up all of the values. If I use Sum(NZ([...]),0) will that work? Thanks

SELECT Sum(NZ([LU Discarded pounds estimated Lookup].[Estimated Weight])) AS [Estimated Weight3270], Sum(NZ([LU Discarded pounds estimated Lookup_1].[Estimated Weight])) AS [Estimated Weight2500], Sum(NZ([LU Discarded pounds estimated Lookup_2].[Estimated Weight])) AS [Estimated Weight3260],

[Stewart Ross]

Sum(Nz(..., 0)) for each item is indeed the way to go. What this does is replace any individual nulls (missing values) with zeros, which can be summed correctly to produce an overall total for each discrete field concerned.

Using Nz(...) on its own without specifying a return value returns an empty string - "" - in place of the null; you cannot sum empty string values!

Be aware that Nz() actually returns a string value. Access does a conversion of the string 0 returned by Nz(..., 0) to a number on the fly before summing.

All of these points are covered in the help file I mentioned in my previous post.

-Stewart