Simple question that's driving me nutzoid!

[frosttr]

Trying to create a calculated field in the detail area of a form, that will calculate each record as I enter it.
The equation should be: =IIf([PH]=3,[QTY]*[VOLTS]*[AMPS]*[HRS]*[UF]*[DF]*1.73, [QTY] * [VOLTS] * [AMPS] * [HRS] * [UF] * [DF] ).
The formula excepted without any error codes - but when I load the report, the field shows blank. I've made sure of my formatting in regards to each field, as well as the output field. If I just do a simple equation like [QTY]*[VOLTS], I get the correct response. But if I add *[AMPS] to the equation, it comes back blank. Can Access not calculate multiple fields that a anything other than + or -?
I know there is likely a very simple response to this, but I've only got 3 hairs left on my head as a result, and would rather keep them! Does anyone have a solution for this?


Thanks

[davegri]

If any of those fields are null, your result is null.
To avoid that, use the nz function to convert nulls to zero (which would give you zero result if any null is present, obviously).
=IIf(nz([PH])=3,nz([QTY]) * nz([VOLTS]) * nz([AMPS]) * nz([HRS]) * nz([UF]) * nz([DF]) * 1.73, nz([QTY]) * nz([VOLTS]) * nz([AMPS]) * nz([HRS]) * nz([UF]) * nz([DF])).

[frosttr]

Thanks for your fast response. I tried the edited formula you suggested, but I still get a "0" return where a value is expected.
I'm attaching a snippet of the design view with the builder open so you can see. I just copied and pasted the formula, so unless I missed something, it should work.

Thanks again for your help

[davegri]

You do realize that if any multiplicand is zero, the result will be zero?

[frosttr]

Ok, that's what I was trying to avoid. I understand that X*Y where X is zero the results will always be 0...but in a form where the fields are calculated, X may not always be 0. So there has to be some way to circumvent the concept of 0 fields. Would it work if I were to put a default value of .0001? It would display as 0, but since there is a value it would not calculate as zero. And even the calculations would not be impacted in final results significant to cause a count total change. I'm really grasping at straws here and have to find some way to make this work for operations. My only other alternative is to do multiple tables/forms/reports, where one is no zero values and the other is zero values...and that would create a logistical nightmare, resulting in multiple joins and links. Anyway, suggestions greatly appreciated!

[Gicu]

Can you maybe show us some sample data with and without missing values and the corresponding expected results?

Cheers,

[davegri]

Maybe this would work for you. Instead of converting nulls to zero, convert them to 1. That way, it won't skew the other calculations.
=IIf(nz([PH])=3,nz([QTY],1) * nz([VOLTS],1) * nz([AMPS],1) * nz([HRS],1) * nz([UF],1) * nz([DF],1) * 1.73, nz([QTY],1) * nz([VOLTS],1) * nz([AMPS],1) * nz([HRS],1) * nz([UF],1) * nz([DF],1)).

[Missinglinq]

That's how you have to use Nz() when doing multiplication or division.

LINQ ;0)>

[frosttr]

I'm attaching a dummy test dBase that I am using to setup the actual dBase. Understand this is a 'rough shell' and still very much a work in progress. However, it will be the base for the actual dBase that I end up creating.
My primary concentration is the calculations for the form 'Study Usage Calculations', which is pulled from the table 'Client' to create the report 'Study Usage Calculation'.
If you need additional info, let me know - I try to respond within an hour, depending on workload.
Thanks so much for any input assistance!

[Gicu]

Does Dave's solution work or you still need help? If yes maybe show us an example with some values and expected result.

Cheers,

[mike60smart]

Hi

This works but you need to look at your Client Table which needs to be Normalised to get rid of Repeating Grousp

[davegri]

Here's the problem. The fields are not null. They actually contain the value zero, so the NZ is ineffective. For it to work, the blank (zero) fields must contain nothing:

[frosttr]

That still didn't work - for the most part. If you look at the form Study Usage Calculations, there are 2 fields that did calculate a value. But none of the other fields calculated (records 30 & 34).

[mike60smart]

Hi

Have you looked at my solution?

[frosttr]

I looked at the excerpt you sent - not sure what you are referring to in as far as 'normalizing to get rid of repeating groups' though. When I viewed the associated form, there are only 2 that calculated, the other 4 were zero value. In the form, anything in description that is all in caps is a room designation, everything else is equipment that uses power and needs to be calculated. The equation for calculations is based off of the PH factor...anything that is a PH of 3 is WATTHRS=QTY*Volts*AMPS*HRS*UF*DF*1.73. If PH=1 (or in rare cases 2), then the equation would be the same, except for the *1.73 at the end. Have I succeeded in total confusion for you? This is all the data I have to work with. The prior programmer did this in DOS back in 1988, and can't be contacted for help, so I'm trying to get this converted into Access and to be as user friendly as possible, since several people will be using it. The calculation for this particular segment has me totally stumped - if I can ever figure it out, or even a work-around, I can do the rest of the calculations, reports, etc, with switchboarding. I haven't worked in DOS for more than 20 years, and because of locks, can't get into the programming to see how it was done. Plus, all company records for the past 30 years are in there, and I don't want to jeopardize corrupting it and losing everything. I'm just hoping someone out there can help me with a solution to this. I've worked in Access enough to know that "nothing is impossible", just a matter of "tricking" it to give the results you want. I've just not found that trick yet for this equation. I had thought as a last resort of doing this portion in Excel, converting formulae to number, then importing to Access. But that's an "iffy" solution, not to mention somewhat time consuming.
Thanks for any input you can give me!