more or less than 0 or blank
in [General]
|
Prev: maximum number of indexes per SQL table that Access (jet) can deal with?
Next: Problem while trying to open access training course on microsoft w
From: Eva on 7 Apr 2010 17:38 Hi I have a column where I have a numbers more than 0, numbers less than 0, 0's and blanks. I would like to put in the formula in the query to calculate those numbers, but only for items more or less than 0, for those that are 0 or blanks I need to put 0 in the formula. I tried <>0, but it doesn't catch blanks. Can you help? -- Please click "yes" if this post helped you! Greatly appreciated Eva
From: Daniel Pineault on 7 Apr 2010 18:14 To handle 'blanks' you should use the Nz() function Nz(YourFieldName, 0) This will automatically return a value = 0 when the field is Null. Once this is inplace, you should be able to perform your calculations. If you require further assitance, please explain your problem further. What do you need to calculate? -- Hope this helps, Daniel Pineault http://www.cardaconsultants.com/ For Access Tips and Examples: http://www.devhut.net Please rate this post using the vote buttons if it was helpful. "Eva" wrote: > Hi > I have a column where I have a numbers more than 0, numbers less than 0, 0's > and blanks. I would like to put in the formula in the query to calculate > those numbers, but only for items more or less than 0, for those that are 0 > or blanks I need to put 0 in the formula. I tried <>0, but it doesn't catch > blanks. > Can you help? > -- > Please click "yes" if this post helped you! > > Greatly appreciated > > Eva
From: Eva on 7 Apr 2010 18:39 Hi Thank you for your replay. I tried NZ and it didn't work, but IsNull works fine, but now I have next problem. I have two columns with numbers but when I sum these two columns and run a query it prompt always the columns headings. I don't know why. There are no blanks, all are numerical. Can you help? -- Greatly appreciated Eva "Daniel Pineault" wrote: > To handle 'blanks' you should use the Nz() function > > Nz(YourFieldName, 0) > > This will automatically return a value = 0 when the field is Null. Once > this is inplace, you should be able to perform your calculations. > > If you require further assitance, please explain your problem further. What > do you need to calculate? > -- > Hope this helps, > > Daniel Pineault > http://www.cardaconsultants.com/ > For Access Tips and Examples: http://www.devhut.net > Please rate this post using the vote buttons if it was helpful. > > > > "Eva" wrote: > > > Hi > > I have a column where I have a numbers more than 0, numbers less than 0, 0's > > and blanks. I would like to put in the formula in the query to calculate > > those numbers, but only for items more or less than 0, for those that are 0 > > or blanks I need to put 0 in the formula. I tried <>0, but it doesn't catch > > blanks. > > Can you help? > > -- > > Please click "yes" if this post helped you! > > > > Greatly appreciated > > > > Eva
From: KARL DEWEY on 7 Apr 2010 19:05
>>but IsNull works fine, I think you need to test some more as IsNull returns a zero if it Is Not Null and a minus one for null. In design view set fields like this to test -- Test: SomeField Test_For_Null: IsNull([SomeField]) >>it prompt always the columns headings. Post your SQL by opening query in design view, click on VIEW - SQL View, highlight all, copy, and paste in a post. Someone can check it for you. -- Build a little, test a little. "Eva" wrote: > Hi > Thank you for your replay. I tried NZ and it didn't work, but IsNull works > fine, but now I have next problem. I have two columns with numbers but when I > sum these two columns and run a query it prompt always the columns headings. > I don't know why. There are no blanks, all are numerical. > Can you help? > -- > > > Greatly appreciated > > Eva > > > "Daniel Pineault" wrote: > > > To handle 'blanks' you should use the Nz() function > > > > Nz(YourFieldName, 0) > > > > This will automatically return a value = 0 when the field is Null. Once > > this is inplace, you should be able to perform your calculations. > > > > If you require further assitance, please explain your problem further. What > > do you need to calculate? > > -- > > Hope this helps, > > > > Daniel Pineault > > http://www.cardaconsultants.com/ > > For Access Tips and Examples: http://www.devhut.net > > Please rate this post using the vote buttons if it was helpful. > > > > > > > > "Eva" wrote: > > > > > Hi > > > I have a column where I have a numbers more than 0, numbers less than 0, 0's > > > and blanks. I would like to put in the formula in the query to calculate > > > those numbers, but only for items more or less than 0, for those that are 0 > > > or blanks I need to put 0 in the formula. I tried <>0, but it doesn't catch > > > blanks. > > > Can you help? > > > -- > > > Please click "yes" if this post helped you! > > > > > > Greatly appreciated > > > > > > Eva |