Need Access formula

[wedor]

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.

 

[Gram123]

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]

Perhaps the field is null when there is nothing in it? Try "is null" instead of <=0

?

 

[wedor]

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.

 

[downwitchyobadself]

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

   =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

   =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

=(field1*field2)*Nz([field3],1)

Hope this does it for you.

 

[Anne Troy]

See that D? I rope 'em in, you finish 'em off. I'd heard of Nz at least once! LOL

 

[wedor]

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!

 

[Anne Troy]

wedor, when has TSG ever let you down? LOL! (DWYBS is the best!)

 

[wedor]

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]

Wedor, what do you mean? I don't have problems, do you? What browser/version are you using?

 

[wedor]

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]

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]

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]

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]

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.