Calculations where one value is zero

[Tumby]

HELP!

I am having problems with a calculation where one of the values is sometimes zero.

Part of the calculation is a 'MeterMultiplier' which can vary from customer to customer, however sometimes the value is zero.
The calculations work fine as long as the MeterMultiplier is not zero.
When the 'MeterMultiplier' is zero, the answer is £0.00 even tho'there are other values being used in the calculation.

My calculation-
=[DayUnitsUsed]*Forms!frmBillCheck!frmlElectricRates.Form!DayUnitCost/100*Forms!frmBillCheck!frmlElectricRates.Form!MeterMultiplier

I have coloured the value that is sometimes zero.

As you can tell I am a novice, and would be greatful to anyone who can put me right!
Thanks!

 

[RuralGuy]

What do you want to happen if the MeterMultiplier is zero? Is the control where this is the ControlSource, located on the frmBillCheck FORM?

 

[Tumby]

If the MeterMultiplier is zero- I still want the other values to calculate an answer- ie, UnitsUsed x UnitCost divided by 100 as the unit cost is in pence per unit and I want the answer in £'s.

I have a form-BillCheck with a 2 subforms, one Rates the Other BillInput.
The MeterMultiplier is in the Rates subform.

The Control is on a subform within the BillCheck form.

 

[RuralGuy]

Try:
=IIF(frmlElectricRates.Form!MeterMultiplier <> 0,
[DayUnitsUsed]*frmlElectricRates.Form!DayUnit Cost/100*frmlElectricRates.Form!MeterMultiplier,[DayUnitsUsed]*frmlElectricRates.Form!DayUnit Cost/100)

 

[Tumby]

Thanks for that RG but it didn't work.

I have notices that my Main form is not updating.
I tried to do the calculations in my main form but I keep getting #Name error.
The calculations work when I put them in the controls of the subform using the subforms of both BillInput and Rates (except for the zero problem)
In the main form-
I have tried using the tables as the control source for the calculations and that doesn't work, neither does using the forms as the control source for the calculations.
BUT I can use the subform for the calculations.
PLEASE WHAT AM I DOING WRONG!

 

[RuralGuy]

You said it did not work. What did it do, throw an error of some sort? Forms are displayed on other forms by means of a SubFormControl. It is the name of the SubFormControl that is used in the syntax. The SubFormControl does not necessarily have the same name as the form it is displaying.
SubFormControl.FORM.ControlName is the syntax I used.

 

[Tumby]

Hi again RG,
Sorry to be such a pain!

This is what I am using-

=IIf(Forms!frmBillCheck!frmlElectricRates.Form!MeterMultiplier<>0,[DayUnitsUsed]*Forms!frmBillCheck!frmlElectricRates.Form!DayUnitCost*Forms!frmBillCheck!frmlElectricRates.Form!MeterMultiplier/100)
This produces a blank control answer where the MeterMultiplier is 0 but produces the correct answer where there is a MeterMultiplier.

This is the formula without the IIF statement.

= [DayUnitsUsed] * Forms![frmBillCheck]![frmlElectricRates].Form![DayUnitCost] * Forms![frmBillCheck]![frmlElectricRates].Form![MeterMultiplier] / 100
This produces £0.00 in the control box where the MeterMultiplier is 0.

 

[RuralGuy]

Why didn't you use the code I supplied? Just copy this code and put it in the ControlCource of the TextBox:

=IIF(frmlElectricRates.Form!MeterMultiplier <> 0, [DayUnitsUsed]*frmlElectricRates.Form![DayUnit Cost]/100*frmlElectricRates.Form!MeterMultiplier, [DayUnitsUsed]*frmlElectricRates.Form![DayUnit Cost]/100)

As I stated earlier, you do not need to go through the FORMS collection to reference these controls.

 

[Tumby]

Hi again!

That did work up to a point- THANK YOU

But the result is the answer needs to be multiplied by 100 to get the correct answer.
example- My answer is .3351598 and should be £335.16. but the records with a multiplier are correct.
If I were to delete one of the '/100' in the formula, records with a MeterMultiplier change accordingly so are incorrect.

I have Properties set as-
MeterMultiplier.......FieldSize-Double - GeneralNumber
DayUnitCosts.........FieldSizeDouble - GeneralNumber
CostOfDayUnits......Currency

The reason DayUnitCosts is not Currency is because the Rate comes as a pence per unit and it would mean the user would have to enter £0.0?????
and this is open to errors.

Thank you for being so patient.

 

[RuralGuy]

It sounds like you need to multiply by 1000 when the MeterMultiplier is 0.

=IIF(frmlElectricRates.Form!MeterMultiplier <> 0, [DayUnitsUsed]*frmlElectricRates.Form![DayUnit Cost]/100*frmlElectricRates.Form!MeterMultiplier, [DayUnitsUsed]*frmlElectricRates.Form![DayUnit Cost]/100*1000)

 

[Tumby]

That works fine for the zero MeterMultipliers but ups the cost by £1000 for the customers with a MeterMultiplier.

 

[RuralGuy]

What I gave you would only affect the zero MeterMultiplier records. Post the code you are using.

 

[Tumby]

=IIf(Forms!frmBillCheck!frmlElectricRates.Form!MeterMultiplier<>0,[DayUnitsUsed]*Forms!frmBillCheck!frmlElectricRates.Form!DayUnitCost*Forms!frmBillCheck!frmlElectricRates.Form!MeterMultiplier/100*1000)

Believe me, I did copy and paste.
If I take out the 'frmBillCheck ' I get an #Name error.

 

[MSAccessRookie]

HELP!

I am having problems with a calculation where one of the values is sometimes zero.

Part of the calculation is a 'MeterMultiplier' which can vary from customer to customer, however sometimes the value is zero.
The calculations work fine as long as the MeterMultiplier is not zero.
When the 'MeterMultiplier' is zero, the answer is £0.00 even tho'there are other values being used in the calculation.

My calculation-
=[DayUnitsUsed]*Forms!frmBillCheck!frmlElectricRates.Form!DayUnitCost/100*Forms!frmBillCheck!frmlElectricRates.Form!MeterMultiplier

I have coloured the value that is sometimes zero.

As you can tell I am a novice, and would be greatful to anyone who can put me right!
Thanks!

The problem stems from a conflict between the the rules of multiplying by zero, and your need to get a result even when one of the numbers being multiplied is zero. RuralGuy's Answer should give you what you you are looking for. You should be able to use it just the way that he wrote it. I believe that the following also does what you want:

=[DayUnitsUsed]*Forms!frmBillCheck!frmlElectricRates.Form!DayUnitCost/100*IIf(Nz(Forms!frmBillCheck!frmlElectricRates.Form!MeterMultiplier)=0,1,Forms!frmBillCheck!frmlElectricRates.Form!MeterMultiplier)
​

The Nz() Function as written will substitute a value of 0 for any record with a Null Value, and the IIf() statement surrounding it will return either a 1 or the original value. Multiplying by 1 will give the appropriate result whenever the multiplier is 0 or Null.

 

[Tumby]

Thanks MS Access Rookie-
I tried your formula but the form will not accept it- just jumps back to what I had before.
I really do appreciate all the help this forum gives.
With this forums help I completed my first database back in February, my client was so impressed with it- he's back for more!!!!!

That's what I am doing now!

 

[MSAccessRookie]

Thanks MS Access Rookie-
I tried your formula but the form will not accept it- just jumps back to what I had before.
I really do appreciate all the help this forum gives.
With this forums help I completed my first database back in February, my client was so impressed with it- he's back for more!!!!!

That's what I am doing now!

On a Dummied Form that code appears to work for me. Please let us know exactly what you typed so that we might be able to see any differences.

 

[RuralGuy]

=IIf(Forms!frmBillCheck!frmlElectricRates.Form!MeterMultiplier<>0,[DayUnitsUsed]*Forms!frmBillCheck!frmlElectricRates.Form!DayUnitCost*Forms!frmBillCheck!frmlElectricRates.Form!MeterMultiplier/100*1000)

Believe me, I did copy and paste.
If I take out the 'frmBillCheck ' I get an #Name error.

You are missing a whole section of the formula.
The formula syntax is: IIF(Test , TestTrue, TestFalse)
In your case you do not have the TestTrue part.

 

[Tumby]

Sorry RG but I just copied and pasted your formula and the missing part doesn't seem to be there.

Where do I put the IIF(Test , TestTrue, TestFalse) syntax?

I'm sorry to be such a novice you really have been very helpful and I do appreciate it.

Thanks.

 

[RuralGuy]

=IIF(frmlElectricRates.Form!MeterMultiplier <> 0,
[DayUnitsUsed]*frmlElectricRates.Form![DayUnit Cost]/100*frmlElectricRates.Form!MeterMultiplier,
[DayUnitsUsed]*frmlElectricRates.Form![DayUnit Cost]/100*1000)

I've separated the lines so it is easier to read but you will have to remove the vbCrLf's.