Need Access formula

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 
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
 

Anne Troy

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

?
 

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.
 
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.
 

Anne Troy

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

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!
 

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.
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
Wedor, what do you mean? I don't have problems, do you? What browser/version are you using?
 

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.
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
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!
 

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.
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
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.
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top