1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Need Access formula

Discussion in 'Business Applications' started by wedor, Dec 3, 2001.

Thread Status:
Not open for further replies.
Advertisement
  1. wedor

    wedor Thread Starter

    Joined:
    Nov 7, 1999
    Messages:
    4,504
    Alright, maybe this is simple for some but I haven't gotten into this part of Access or VB. I have a table that has a field( I'll call it field 3) that sometimes has a value and sometimes doesn't, I was using this field to do a small calculation in a report. The problem comes in when there is no data in the field it returns a zero. The expression for the report text box is "=(field1*field2)*field3", which works great as long as the value in field3 is 1 or more. How do I tell Access to use "1" if field3 is <=0 ?? My wife seems to recall that she has done this in a VB class she took and it certainly seems possible, I just don't know where to start. Any help appreciated as always.
     
  2. Gram123

    Gram123

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    One option would be to use an IIF function:

    =IIF([field1]*[field2]*[field3])=0,"1", ([field1]*[field2]*[field3])

    I think that's correct. It's basically an If, Then, Else function, so:

    If field1 * field 2 * field3 = 0, Then show one, Else do the multiplication.

    I'll check this in the morning and post back (I have a minor doubt about the parenthesis) unless someone else comes in and corrects/confirms the above formula, or comes up with a better solution.

    HTH
    Gram
     
  3. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Perhaps the field is null when there is nothing in it? Try "is null" instead of <=0

    ?
     
  4. wedor

    wedor Thread Starter

    Joined:
    Nov 7, 1999
    Messages:
    4,504
    Thanks for the replies. I need the formula to use the "1" in the formula, not return a 1, unless I am misunderstanding what you have written. For instance if field1 = 3 and field2 = 24.00 and field3 =0, it returns 0 as the answer and I need it to return 3*24.00 or 72.00. Field3 can sometimes have a number from 1 to 20 in it but sometimes has nothing, I have "cheated" by putting a 1 in every blank field but I need a long term solution as I will be creating new tables from the same source this as one, and would have to paste 1's in thousands of cells.
     
  5. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    Okay, for clarity's sake: Either
    1. Your field has absolutely no value in it, which non-value is called null, programmese for non-existent. (I'm assuming that the Accept Zero Length property of Field3 in question is set to "no", as it should be.) You use the Nz function to deal with this problem, it looks like this
      Code:
      =Nz([Field3],1)
      which in plain English means "If Field3 has a value leave it alone, if it has no value use 1 instead.
    2. Or your field has 0 and you want it to have 1 as a value. Different; in this case you might have to test for 0 and for null. You use the iif function for that, here's the syntax
      Code:
      =iif(IsNull([Field3]) or [Field3]<=0, 1, [Field3])
      which means if Field3 is empty or not > 0, replace it with one, otherwise leave it alone. (iif has three parts: test, truepart, falsepart.)
    Either one of these should be dropped into your formula as a replacement for [Field3] only. So for example
    Code:
    =(field1*field2)*Nz([field3],1)
    Hope this does it for you.
     
  6. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    See that D? I rope 'em in, you finish 'em off. I'd heard of Nz at least once! LOL
     
  7. wedor

    wedor Thread Starter

    Joined:
    Nov 7, 1999
    Messages:
    4,504
    Thanks! that looks like "exactly" what I was looking for! I have only been working in Access since August so I'll probably be back looking for more help. Thanks again!
     
  8. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    wedor, when has TSG ever let you down? LOL! (DWYBS is the best!)
     
  9. wedor

    wedor Thread Starter

    Joined:
    Nov 7, 1999
    Messages:
    4,504
    Hmmmm, TSG has never let me down on answers,..... browsing since they added the new pop-ups, now that's a different matter. It takes me 3 clicks to go back in the forums now, I used to be able to navigate normally. It makes browsing here very tedious and has caused me not to come as often. Hopefully they will fix this in the near future.
     
  10. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Wedor, what do you mean? I don't have problems, do you? What browser/version are you using?
     
  11. wedor

    wedor Thread Starter

    Joined:
    Nov 7, 1999
    Messages:
    4,504
    I'm using IE6, when I click back I see information referring to burstnet flash across the bottom of the screen and then I end up going nowhere, if i click again, same result, on the third click I finally go back to the previous page.
     
  12. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    I'm using IE6 too, wedor.

    Have you tried running the repair? Go to your control panel, hit Add/Remove programs, click IE6, then Add/Remove button. Choose the repair option. It should reset anything or anything like that. I've used it before. I've never had the problem you're talking about. Let's get it taken care of so you don't have to avoid us!
     
  13. wedor

    wedor Thread Starter

    Joined:
    Nov 7, 1999
    Messages:
    4,504
    OK, I tried that, no go. I am using some lines in the hosts file to block pop-ups, other than that nothing out of the ordinary.
     
  14. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Can you remove those lines to test if it otherwise works? Then we can, hopefully, rule that out or find out that they are causing the problem...

    PS: Sorry to be spamming all you Access'ers out there. I can move this thread.
     
  15. wedor

    wedor Thread Starter

    Joined:
    Nov 7, 1999
    Messages:
    4,504
    There are quite a few, I'm using a trick that uses this to block pop-ups, "127.0.0.1 www.pop-upURL" where pop-up URL is just that, the URL of the pop-up. I don't see adds when I am browsing here, I am reluctant to remove any of it as I have grown quite comfortable browsing without annoying adds popping up in my face.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/60587

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice