I have a report that gives me a Total Record for each record as well as a Total Report that addes the Total Records. I am also needing to Divide Total Report with another Total Report Field (I'll call Them Total Report A and Total Report B) This should give me a Percent Answer. The Problem I am having is that A/B does not equal C.

Here is my Program for the data Field that I want the Percent to Appear In.

IIF([TotalReportA]=0,0,Round([TotalReportA/TotalReportB],2))

The Resulting Answer gives me something like this...

Total Report A =15
Total Report B =152.75
Answer Given =1.88
Answer Supposed to Be =0.098199 (This answer from my Calculator)

What am I missing or doing wrong?

Thank you for your help on this problem

Robert M

What data types are you using? Single and doubles suffer from floating point errors and if you want good precision you should go with the Currency datatype or Number data type set to be Decimal.

Thanks Bob, Good to hear from you again.
What I am using is Standard with 2 Decimal Points. as Some of the amounts are 10.50 or 9.35. I will try the numbers as there is no currency in the program.

Thank you again for all your help with my troubles.

Robert M

Thanks Bob, Good to hear from you again.
What I am using is Standard with 2 Decimal Points. as Some of the amounts are 10.50 or 9.35. I will try the numbers as there is no currency in the program.
Actually, Look in the table (not the form), and there SHOULD be Currency listed:

http://downloads.btabdevelopment.com/screenshots/currencydatatype01.png

While Single and Double do give some floating point errors, that one seems a lot further amiss than I would have expected.

Are you quite sure you are referring to the right controls on the correct subreports?

Not much to go on but could it be that the ReportTotalB is returning the total field directly from the current record rather than the control which is calculating Sum(totalfield)?

My Appologies Bob, I meant that I am using strickly numbers in my database and no currency.

I have gone in a set the Data type in the Tables to Number (which it was set to) and the sub section of that to...
Field Size = Decimal
Format = Fixed (the user is wanting the nubmers to be 10.00 or 10.50 rather than 10. or 10.5)
Precision = 18
Scale = 0
Decimal Places = 2

The Results after applying the above is

A=15
B=152
C=1.88% (it should show as a percent)

C Should show a 9.87 %

My Formula for the C Field is...

=IIf([A]=0,0,([A]/[B]))

Thanks for the help and suggestions and most importantly your patience on this
Bob and Galaxiom

Robert M

Note: I just noticed on the line above my grand total I have...

A=15
B = 54
C=1.88%

Me thinks there is a Major Glich in the way I have the report setup. What do y'all think? rm

Might be easier if we can look at the database itself.

Database is kinda large, as It has about 45 entities with their CPE hours Figured for 12 Seperate Catagories over three seperate years. each year having to total then a grand total at the end. So

At the end of each year I have
'A' being =Sum([Total PD]) under the respected Catagory
And [RecordTotal] being a Query Generated field that totals the individual records that 'B' being =Sum([RecordTotal]) sums up.

Then for the grand total I have pretty much the same deal with
=Sum([Total PD]) being the Grand Total of all three years of that catagory and
=Sum([RecordTotal]) being the Grand Total for all the [TotalRecord] fields I have in the database.

If I could I would send you a sample of the report and how it is set up.

Thanks for your help on this Bob.

Robert M

Got it working, not quite sure how exactly.
Using my WorkTable/Experimental Database Copy I started at the tables and went through all the number fields as...

Field Size = Integer
Format = Fixed
Decimal Places = 2

I then went through the Queries and set the number fields as...

Format = Fixed
Decimal Places = 2

And Finally in the Report I set the Fields as...

Format = Fixed
Decimal Places = 2

Looking back and knowing that even a single item in a field name like "Best" and "best" will cause the computer to think there are two fields, I guess so with the setting up of the Number Field, having to make sure each field, subtotal and total in the Report are setup identical to the Table and or query.

To those that know more than I, Does that sound about right?

Robert M

ps. for whatever the reason, I did the above and got the report to come out calculated correctly. I thank those that helped me with this problem. rm

I started at the tables and went through all the number fields as...

Field Size = Integer
Format = Fixed
Decimal Places = 2


Do you realize that Integers do not have decimal places (they are WHOLE numbers) so setting it to fixed, two decimal places guarantees ANY number entered will be rounded to the nearest whole number and will ALWAYS have .00 as the extension.

Oops!...Math Class was So LOOOOooooong ago... Thanks for the heads up I'll make the adjustments, hopefully the program will still work.

check the field names - if you are doing this in subs and grand totals, it wont work - as it nneds to use the field names in the relevant section - that is probably the problem

The problem was not the field names gemma-the-husky, but rather the setup of the numeric forms. Out of sheer desperation I went through the tables, queries, forms and reports making sure every single form that was numeric was set to the same Format (Fixed) and the decimal place was set at 2. Works perfectly now. Thanks for your suggestion.

Robert M