 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / February 2007multiplying a null value
I have an unbound field [NetWt] that multiplies 3 other fields: [UnitCount]*[SubUnitCount]*[DecWtg] The problem is that [SubUnitCount] is sometimes NULL therefore, the return is NULL. How can I code [NetWt] to multiply even when [SubUnitCount] is NULL? Thanks!!!  Signaturewww.Marzetti.com > I have an unbound field [NetWt] that multiplies 3 other fields: > [UnitCount]*[SubUnitCount]*[DecWtg] [quoted text clipped - 5 lines] > > Thanks!!! What value do you want subunitcount to be when it's Null? I suppose the most logical values are 1 (no effect) and 0 (if there are no subunits, total value is 0). You can achieve this with [UnitCount]*Nz([SubUnitCount],X)*[DecWtg] Where X=0 or 1, depending on which you want. Hi, Bruce. > What value do you want subunitcount to be when it's Null? That's a tricky one because there are times when it HAS to be NULL. Are you saying that is HAS to have a value in order to properly multiply? > I suppose the > most logical values are 1 (no effect) and 0 (if there are no subunits, [quoted text clipped - 4 lines] > > Where X=0 or 1, depending on which you want. I experimented with this: [UnitCount]*Nz([SubUnitCount],0)*[DecWtg] and entered "0" in [SubUnitCount] but the return was NULL. Ultimately, this isn't going to work for me but I wanted to try it out. I guess there's no getting around a NULL value in [SubUnitCount]...? I suppose I could change all of the NULLS to "0" for when they're null but what is the reason why the return is NULL? > Hi, Bruce. > >> What value do you want subunitcount to be when it's Null? > > That's a tricky one because there are times when it HAS to be NULL. Are you > saying that is HAS to have a value in order to properly multiply? Yes ... you can't multiply by something that isn't a number. That's why I suggested substituting 1 as the value, since multiplying by 1 won't affect the outcome. >> I suppose the >> most logical values are 1 (no effect) and 0 (if there are no subunits, [quoted text clipped - 10 lines] > and entered "0" in [SubUnitCount] but the return was NULL. Ultimately, this > isn't going to work for me but I wanted to try it out. That's strange; it sounds like there's a NULL somewhere else involved. It should return zero if SubUnitCount is NULL and the other two fields have values. If you want a zero value for the whole thing when any part of it is NULL, you could just use Nz([UnitCount]*[SubUnitCount]*[DecWtg],0) > I guess there's no getting around a NULL value in [SubUnitCount]...? I > suppose I could change all of the NULLS to "0" for when they're null but what > is the reason why the return is NULL? That depends on your data, and how your tables are designed. What do NULL values "mean" in your database -- does a NULL mean that the value is unknown or missing, or does it mean there are no subunits? Depending on what a NULL means, you would treat it differently. That's not clear so far from your question. Thanks again, Bruce! Signaturewww.Marzetti.com > > Hi, Bruce. > > [quoted text clipped - 38 lines] > on what a NULL means, you would treat it differently. That's not clear > so far from your question. Whooops! I found my error and corrected. I've also added in order to convert to lbs.: =[UnitCount]*Nz([SubUnitCount],0)*[DecWtg]*0.002205 This returns 0.00 when [SubUnitCount] is 0. It returns correctly when [SubUnitCount] is 1 or more. I expected it to return properly when [SubUnitCount] is 0. Do you see any reason why it's not? Thanks! > Whooops! I found my error and corrected. > [quoted text clipped - 6 lines] > I expected it to return properly when [SubUnitCount] is 0. Do you see any > reason why it's not? If you multiply anything by zero, it will be zero. What is "returning properly"? It sounds like what you want is [UnitCount]*Nz([SubUnitCount],1)*[DecWtg]*0.002205 because multiplying by one won't change the value. But if there are records where SubUnitCount=0 you should either change them to NULL (if that is consistent with the logic of your application) or write a more complicated return function that checks for the 0 value. >That's a tricky one because there are times when it HAS to be NULL. Are you >saying that is HAS to have a value in order to properly multiply? Certainly. Null means "This field is undefined, it does not have any value". What is the product of 3 and "I don't know what this value might be, it could be anything"? John W. Vinson [MVP] Well, I'm always surprised/amazed at what Access can do so I thought there might be a way to make this happen, too! Signaturewww.Marzetti.com > >That's a tricky one because there are times when it HAS to be NULL. Are you > >saying that is HAS to have a value in order to properly multiply? [quoted text clipped - 7 lines] > > John W. Vinson [MVP] >Well, I'm always surprised/amazed at what Access can do so I thought there >might be a way to make this happen, too! Well, there is - the NZ function. I guess what I'm wondering is - "what would you EXPECT to happen?" and "how would you expect Access to know what you expect to happen?" John W. Vinson [MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.