Report Field Not Showing Correctly

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a budget database in which I have a report where I have the following
4 fields: [1stQtr]; [2ndQtr]; [3rdQtr]; [4thQtr].
The values for these fields are determined in the query as follows:

1stQtr:
nz(IIf([QtrofPurc]="1",[TotalCost],IIf([QtrofPurc]="Spread",[Spread],"")))
2ndQtr:
nz(IIf([QtrofPurc]="2",[TotalCost],IIf([QtrofPurc]="Spread",[Spread],"")))
3rdQtr:
nz(IIf([QtrofPurc]="3",[TotalCost],IIf([QtrofPurc]="Spread",[Spread],"")))
4thQtr:
nz(IIf([QtrofPurc]="4",[TotalCost],IIf([QtrofPurc]="Spread",[Spread],"")))

TotalCost:
IIf([SalesTax?]=Yes,([Quantity]*[UnitCost])*1.07,[Quantity]*[UnitCost])

Spread: IIf([QtrofPurc]="Spread",[TotalCost]/4,0)

Each of the 4 report fields are formatted “Currencyâ€, with “0†decimal
places. Each of these fields appears correctly on the report.

[QtrofPurc] is a text field since it is not used in any mathematical
computations.

I have a field [ItemTotal] on the report that adds the 4 fields as follows:
=nz([1stQtr],0)+nz([2ndQtr],0)+nz([3rdQtr],0)+nz([4thQtr],0)

The [ItemTotal] field is also formatted “Currency†with “0†decimal places.
Most of the items are budgeted for a specific quarter except from time to
time one will need to be spread over all quarters.

My problem is that I have one line item where the [TotalCost] is entered in
the form as $1,500,000 and is to be “Spread†over all quarters. It correctly
appears as the same amount each quarter ($375,000) being computed in the
[Spread] query field above.
However, the [ItemTotal] amount appears as “375000375000375000375000â€.

I have tried everything I can think of to fix the situation but to no avail.
I am using Access 2007.
Your help is greatly appreciated.
 
I have a field [ItemTotal] on the report that adds the 4 fields as follows:
=nz([1stQtr],0)+nz([2ndQtr],0)+nz([3rdQtr],0)+nz([4thQtr],0)

Try this ---
I have a field [ItemTotal] on the report that adds the 4 fields as follows:
=Sum([1stQtr]+[2ndQtr]+[3rdQtr]+[4thQtr])
 
Sorry, this did not work either. I have also made the computation for the
total in the query and it works OK. However, when I put the field onto the
report it gives me the same repetitive results -375000,375000,375000,375000.

I've never had this type problem before.

KARL DEWEY said:
I have a field [ItemTotal] on the report that adds the 4 fields as follows:
Click to expand...
Click to expand...
=nz([1stQtr],0)+nz([2ndQtr],0)+nz([3rdQtr],0)+nz([4thQtr],0)

Try this ---
I have a field [ItemTotal] on the report that adds the 4 fields as follows:
=Sum([1stQtr]+[2ndQtr]+[3rdQtr]+[4thQtr])

--
KARL DEWEY
Build a little - Test a little


Don S. said:
I have a budget database in which I have a report where I have the following
4 fields: [1stQtr]; [2ndQtr]; [3rdQtr]; [4thQtr].
The values for these fields are determined in the query as follows:

1stQtr:
nz(IIf([QtrofPurc]="1",[TotalCost],IIf([QtrofPurc]="Spread",[Spread],"")))
2ndQtr:
nz(IIf([QtrofPurc]="2",[TotalCost],IIf([QtrofPurc]="Spread",[Spread],"")))
3rdQtr:
nz(IIf([QtrofPurc]="3",[TotalCost],IIf([QtrofPurc]="Spread",[Spread],"")))
4thQtr:
nz(IIf([QtrofPurc]="4",[TotalCost],IIf([QtrofPurc]="Spread",[Spread],"")))

TotalCost:
IIf([SalesTax?]=Yes,([Quantity]*[UnitCost])*1.07,[Quantity]*[UnitCost])

Spread: IIf([QtrofPurc]="Spread",[TotalCost]/4,0)

Each of the 4 report fields are formatted “Currencyâ€, with “0†decimal
places. Each of these fields appears correctly on the report.

[QtrofPurc] is a text field since it is not used in any mathematical
computations.

I have a field [ItemTotal] on the report that adds the 4 fields as follows:
=nz([1stQtr],0)+nz([2ndQtr],0)+nz([3rdQtr],0)+nz([4thQtr],0)

The [ItemTotal] field is also formatted “Currency†with “0†decimal places.
Most of the items are budgeted for a specific quarter except from time to
time one will need to be spread over all quarters.

My problem is that I have one line item where the [TotalCost] is entered in
the form as $1,500,000 and is to be “Spread†over all quarters. It correctly
appears as the same amount each quarter ($375,000) being computed in the
[Spread] query field above.
However, the [ItemTotal] amount appears as “375000375000375000375000â€.

I have tried everything I can think of to fix the situation but to no avail.
I am using Access 2007.
Your help is greatly appreciated.
Click to expand...
Click to expand...
 
Back
Top