calculated fields in Cross tab reports

  • Thread starter Thread starter nms
  • Start date Start date
N

nms

I have successfully created the calculated fields that I need for my report
to sum the columns but now I need to divide one field by another to get a
percentage in the rows (which I have also successfully calculated for the
individual fields) and all I get is a number 1. Is division done differently
in cross tab reports?

The numbers are:

1316/1337 = 98% (for the total row)

My control sources:

=(Nz([4],0)+Nz([5],0))/(Nz([1],0)+Nz([2],0)+Nz([3],0)+Nz([4],0)+Nz([5],0))

or (I gave the text boxes names and get the same results)

=([totalnoresolved])/([totalnoopened])
 
The problem here could be to do with the data types.

Are you certain that all text boxes contain numbers only? There's no chance
of the source query generating text such as "N/A" for some cases?

If they are all numbers (or null), set the Format property of all the text
boxes to a numeric format, such as:
General Number
That can help Access understand the intended data type. Once it knows they
are numbers, it can perform the division.
 
Hi Allen-it was my fault-I had the decimal set to 0. Once I changed it to 2
decimal points, it worked great-mea culpa...
Allen Browne said:
The problem here could be to do with the data types.

Are you certain that all text boxes contain numbers only? There's no chance
of the source query generating text such as "N/A" for some cases?

If they are all numbers (or null), set the Format property of all the text
boxes to a numeric format, such as:
General Number
That can help Access understand the intended data type. Once it knows they
are numbers, it can perform the division.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

nms said:
I have successfully created the calculated fields that I need for my report
to sum the columns but now I need to divide one field by another to get a
percentage in the rows (which I have also successfully calculated for the
individual fields) and all I get is a number 1. Is division done
differently
in cross tab reports?

The numbers are:

1316/1337 = 98% (for the total row)

My control sources:

=(Nz([4],0)+Nz([5],0))/(Nz([1],0)+Nz([2],0)+Nz([3],0)+Nz([4],0)+Nz([5],0))

or (I gave the text boxes names and get the same results)

=([totalnoresolved])/([totalnoopened])
Click to expand...
Click to expand...
 
Back
Top