By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Replace no value with 0

P: 37
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
Nov 8 '08 #1
Share this Question
Share on Google+
5 Replies


DonRayner
Expert 100+
P: 489
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT Sum(Nz([LU Discarded pounds estimated Lookup].[Estimated Weight])) AS [Estimated Weight3270]
  2. 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];
  3.  
Nov 9 '08 #2

Expert Mod 2.5K+
P: 2,545
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
Nov 9 '08 #3

P: 37
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
Nov 10 '08 #4

P: 37
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],
Nov 10 '08 #5

Expert Mod 2.5K+
P: 2,545
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
Nov 10 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.