Divide by zero error - sequence of entered values on form

[razorking]

OK, another one for me to beat my head against a wall on I suppose. I have a form where a user will enter a deposit dollar amount, then a deposit percent, then there is another calculated control on the form that displays for them the total amount of the quoted purchase. The calculated control says:
=[deposit$]/[deposit%]

The problem I am having is: the deposit% needs to be keyed before the deposit$ for it to display correctly. It may be a small thing but, the flow of my form works better if they enter the deposit$ first, then the deposit%

Is there any way I can preserve the flow/sequence of data entered on the form and still show the calculated value correctly?

See attached image files:
1) this one shows the error when one enters the values on the form in the top to bottom sequence.
2) this one shows how it displays correctly if one enters deposit% first then deposit$

 

[SOS]

You can just use a simple method to keep the divide by zero errors out.

Change your formula to something like:

=IIF([FieldNameYHere]=0,0,[FieldNameXHere]/[FieldNameYHere])

 

[razorking]

I should have explained, I have already found this solution but it does not wwork for my specific situation. If I try your suggestion it just shows a $0.00 in the calculated control - see attached.

I think the problem is that as soon as I enter a value in the deposit$ box it performs the calculation, then it displays the 0.00 or the error. Even when I subsequently enter a value in the deposit% box it does not "recalculate". I tried F5 (refresh) but does not help. So somehow it has something to do with the sequence of which the values are entered. Yes I can change the flow of the form so deposit% will be entered before deposit$. I just hoped there might be a simple solution as, again, the form flows better as is.

 

[MSAccessRookie]

I should have explained, I have already found this solution but it does not wwork for my specific situation. If I try your suggestion it just shows a $0.00 in the calculated control - see attached.

I think the problem is that as soon as I enter a value in the deposit$ box it performs the calculation, then it displays the 0.00 or the error. Even when I subsequently enter a value in the deposit% box it does not "recalculate". I tried F5 (refresh) but does not help. So somehow it has something to do with the sequence of which the values are entered. Yes I can change the flow of the form so deposit% will be entered before deposit$. I just hoped there might be a simple solution as, again, the form flows better as is.

If you don't want to show the 0.00, then try this instead (The "" should leave the field Blank):

=IIF([FieldNameYHere]=0,"",[FieldNameXHere]/[FieldNameYHere])[/quote]
​

 

[SOS]

You may want to put a

Me.Recalc

in the after update event of each text box so that it will recalculate after a field has changed.

 

[razorking]

If you don't want to show the 0.00, then try this instead (The "" should leave the field Blank):

=IIF([FieldNameYHere]=0,"",[FieldNameXHere]/[FieldNameYHere])​

​

[/QUOTE]


I want the record to show, but it needs to calculates after the value is entered in the deposit% text box. As I explained, it seems to calculate after the deposit$ text box value is entered. And at that time there is not yet anything in the deposit% text box, but there will be.

 

[razorking]

You may want to put a

Me.Recalc

in the after update event of each text box so that it will recalculate after a field has changed.

I tried this but now when I enter a value in the deposit% text box Access gives me a warning (or error) saying: Database can't find the object 'me.'

 

[SOS]

I tried this but now when I enter a value in the deposit% text box Access gives me a warning (or error) saying: Database can't find the object 'me.'

That's because you put it in the event PROPERTY and not in the VBA window where it should go. See this for where to put code.

 

[razorking]

That's because you put it in the event PROPERTY and not in the VBA window where it should go. See this for where to put code.

You are correct, I did and it did not like it one bit. I have put it in the correct place and it now works!

Thanks!

 

[SOS]

Glad to be able to help