Calculated fields & dividing by Zero

[Mich2too]

Background:
Completed Advanced Access college class
Have not used Modules
Not familiar with Visual Basic
Using Access 2000

Fields being used:
# of Tours - counts number of tours assigned each rep.
# of Sales - counts number of sale made by each rep.
Volume - total of sales made by each sales rep.
VPT (Volume per Tour) - volume divided by # of Tours
Closing - # of Sales divided by # of tours

Due to the nature of the business, a down payment must be made on each sale by the purchaser. IF the full down payment cannot be made, the purchaser is allowed to make payments until the full down payment is made. When this happens, the sales rep assigned the tour is credited with a tour for the day. However, the sales rep is NOT credited with a sale until the full down payment has been made, which may not be for weeks. When this scenario plays out, it is possible for a sales rep to NOT be credited with any tours for the week, but IS credited with a sale. Therefore, the calculation fields contain the word #error when the query is run since it is not possible to divide Zero tours by # of Sales or to divide Volume by Zero tours.

I had created the original reports using generic data and the reports have been laid out. Now that "real world" data has been entered into the database, the reports would no longer run due to the errors in the query. I have now deleted the problem fields from the original query and have moved the calculations to be created into the actual reports. The problems created by this are that the report now prompts the user for VPT information, and Closing % right along with the other user requested input.

I have also tried creating a new query and a new report, but when I create the calculated fields in the new report and try to view it, I get the original message that the expression is too complicated, typed incorrectly etc as we became painfully aware of when the original query contained errors in these fields.

If I could get rid of computer generated prompts for VPT and Closing% when the original report is opened, I'd be in heaven. But that report still shows Div/0 and #error in the result fields accordingly, but is some thing that the user says can be lived with.

Any suggestions would be GREATLY appreciated. I have over 100 hours plus numerous travel expenses incurred in this project. As the stats created by these reports are used in determining the sales rep rotation for the next week, they are a major necessity for the user. Unfortunately, if I can't get the bugs worked out, I will not get paid for ANY of the project.

 

[JerryDennison]

Put the expression back in the query as you had it. Then use an IIf() function to test for zero in the denominator. If there is a zero then set the field null, if not, then pass the expression.

 

[Mich2too]

THANK YOU!!!!! Knew it would be something simple and right under my nose. Just makes me even more determined that attending an upcoming Excel Power User's 2 day class will be helpful to me since both Excel and Access use the same basic formulas and VBA programming.

 

[MichaelRed]

Mich,

This solves the ERROR apearing in your report. It does NOT address the issue of needing to match the values so that the report shows all activity. You STILL need to account for ALL of the SALES and VOLUMES for all TOURS. If you are having divide by zero errors, you are probably missing something.

Typically, sales personel are paid on a commission basis. Think about their reaction if 'they' find out your program is not crediting them properly for their sales.


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over