Unbound Calculations

[mikelev]

I have a report with several unbound textboxes that perform calculations. Text92 provides the correct percentage until I get to a small number (i.e 1.13%, or 0.14%). The correct value should be 1.13% and I am getting 113.05%, or value should be 0.13% and I am getting 13.33%.

Im hoping that someone can see an obvious error, or make a recommendation?

Text80

=nz([Sum Of TOTALDROP],0)

Text85

=nz([credit],0)-nz([fill],0)

WL

=nz([text85],0)+nz([text80],0)

Text100

=nz([text85],0)+nz([text80],0)

Text92

=IIf(([wl]=0),0,IIf(([text80]=0),0,([wl]/[text80])))

Many thanks as always.

 

[dhookom]

Are all of these controls in the same section?

Have you considered renaming Text100, Text80,... to names that have some meaing?

Could you provide some values for your fields?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mikelev]

Howdy Dhookom,

Yes these controls are all in the details section of my report. Like all begining..... (and I say this lightly) "programmers" I had a lot to learn, (and still do) when I created this report some time ago.

The report is based on a query:

SELECT [TABLES Query].SORT, [TABLES Query].DESCRIPTION, [TABLES Query].TABLENUM, [SOFTCOUNT Query].[Sum Of TOTALDROP], [TABLEINV Query].[Sum Of total], FILLCRSUM.SumOfTOTAL AS credit, FILLSUM.SumOfSumOfTOTAL AS fill
FROM (((([TABLES Query] INNER JOIN [TABLEINV Query] ON [TABLES Query].TABLENUM = [TABLEINV Query].TABLENUM) LEFT JOIN FILLCRSUM ON [TABLES Query].TABLENUM = FILLCRSUM.TABLENUM) LEFT JOIN FILLSUM ON [TABLES Query].TABLENUM = FILLSUM.TABLENUM) LEFT JOIN [SOFTCOUNT Query] ON [TABLES Query].TABLENUM = [SOFTCOUNT Query].TABLENUM) INNER JOIN TABLEINV ON [TABLES Query].TABLENUM = TABLEINV.TABLENUM
GROUP BY [TABLES Query].SORT, [TABLES Query].DESCRIPTION, [TABLES Query].TABLENUM, [SOFTCOUNT Query].[Sum Of TOTALDROP], [TABLEINV Query].[Sum Of total], FILLCRSUM.SumOfTOTAL, FILLSUM.SumOfSumOfTOTAL
ORDER BY [TABLES Query].SORT;

Report is layed out like so:

(A)opener
(B)Fills
(C)Credits
(D)Closer
(E)Total = (A+B+C+D)
(F)Drop
(G)Win/Loss  (E+F)
(H)Win/Loss % (G/F)

TextBox Control Source:

(A)Text64 =Nz([sum of Total]),0)
(B)Text54 =Nz([fill]),0)
(C)Text52 =Nz([Credit]),0)
(D)Text66 =Nz([sum of Total]),0)
(E)Text85 =Nz([Credit]),0)-Nz([fill]),0)
(F)Text80 =Nz([sum of TotalDrop]),0)
(G)WL =Nz([Text85],0)+Nz([Text80],0)
(F)Text92 =IIf(([wl]=0),0,IIf(([text80]=0),0,([wl]/[text80])))

Sample Data:

(A)Text64 $10,240 
(B)Text54 $133.00
(C)Text52 $470.00
(D)Text66 $10,240
(E)Text85 $337.00
(F)Text80 $650.00
(G)WL     $987.00
(F)Text92 151.85%  should be 1.51%

As I said before the larger percentages seem to work fine. I appologize for the messy query. I hope you can make sense of this......

Many thanks D I appreciate it!

 

[mikelev]

Any hope for this problem?

 

[dhookom]

987/650 = 151.85%. Why do you think it should equal 1.51%? To get a value of 1.52%, you would need a WL of 9.87.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mikelev]

This is a classic example of looking at the data too long, and assuming the error is within Access. It took someone on the outside looking in to point out the same thing.

The clients hand written report showed 1.51 and the error was there, not in the calculation.


You the man Duane