Calculation issue

[keiath]

I have a subform, the data is fed into the table by import.



As you can see the Y/E arrears has + amounts and - amounts. If they are plus amounts they are in arrears and if they are minus amounts they are in advance, without the need to manually make another column and separate the two numbers

is there a way to calculate only the minus amounts and another calculation for the plus amounts? so that i get the following answers (-610.38) in Advance and (285.03) in arrears

Address	Arrears B/F	Demanded	pportion	Shortfall	Actually Received	Y/E Arrears
Apt 30 Optical Court (095)	0.00	773.73	709.75	-63.98	777.73	-4.00
Apt 31 Optical Court (096)	-36.11	965.45	887.89	-77.56	1103.79	-174.45
Apt 32 Optical Court (097)	-328.69	965.45	887.89	-77.56	1039.02	-402.26
Apt 33 Optical Court (098)	501.92	965.45	887.89	-77.56	1468.35	-0.98
Apt 1 Oasis Court, 1 (099)	0.00	226.66	632.43	405.77	95.35	131.31
Apt 2 Oasis Court, 1 (100)	0.00	230.99	791.46	560.47	238.54	-7.55
Apt 3 Oasis Court, 1 (101)	0.00	272.99	833.46	560.47	119.27	153.72
Apt 4 Oasis Court, 1 (102)	0.00	230.99	791.46	560.47	238.54	-7.55
Apt 5 Oasis Court, 1 (103)	0.00	184.66	632.43	447.77	190.70	-6.04
Apt 6 Oasis Court, 1 (104)	0.00	230.99	791.46	560.47	238.54	-7.55

Thanks

[June7]

Build query with expressions to create fields. Use that query as RecordSource for form or report.

SELECT *, IIf([Y/E Arrears]<0,[Y/E Arrears],Null) AS Advance, IIf([Y/E Arrears]>=0,[Y/E Arrears],Null) AS InArrears FROM tablename;

Textboxes in form or report footer section with expressions:

=Sum([InArrears])

=Sum([Advance])

[keiath]

I am not entirely sure i understand what you mean.

The subform is based on a query table already.

so where would i put the;- SELECT *, IIf([Y/E Arrears]<0,[Y/E Arrears],Null) AS Advance, IIf([Y/E Arrears]>=0,[Y/E Arrears],Null) AS InArrears FROM tablename;

In SQL?

This is the current SQL statement:-

SELECT [Arrears and Income Report].[Site Code], [Arrears and Income Report].Address, [Arrears and Income Report].[Arrears B/F], [Arrears and Income Report].Demanded, [Arrears and Income Report].pportion, [Arrears and Income Report].Shortfall, [Arrears and Income Report].[Actually Received], [Arrears and Income Report].[Y/E Arrears]
FROM [Arrears and Income Report]

[keiath]

Hi June

Never mind, I figured it out, and works great!

Thank you