How to separate positive and negative values in a report

[robnsd]

I have a parameter query that brings up records for a user selected date. One of the fields in each record is amount [Amt]. [Amt] can be either a positive or negative number. I want to create a report based on the query that shows records where [Amt] is greater than zero in one column and records where [Amt] is less than zero in a second column. I know this must be simple but I can not get my head around it.

[JoeM]

Add two calculated fields in your query like this:

PosAmt:IIF([Amt]>0,[Amt],0)
NegAmt: IIF([Amt]<0,[Amt],0)

Now you have two fields/columns, one for positive amounts and one for negative amounts.
If you want to hide the zeroes on your Report, you can do that with Conditional Formatting.

[robnsd]

Thanks. I used your suggestion for the query. But now I want a report with two separate columns, where each entire record for [Amt] greater than zero appears on one side of the report and the entire record for [Amt] less than zero appears on the other side of the report. Two separate columns.

[JamesDeckert]

In your report, one field is called PosAmt, and the other is called NegAmt. Look at the fields available to put on the report.

[robnsd]

I'm sorry if I am not getting this. Maybe I'm not clear enough. My report (in simplified form) would look like this where there would be two columns side by side, on one side only the records that have positive amounts and on the other only records that have negative amounts.

Name.......PosAmt Name.......NegAmt

[June7]

If the Name value is not supposed to be the same on both sides of one row, like:

Name	Amt		Name	Amt
f	5		d	-1
g	4		e	-3
i	7		r	-1
k	2		w	-2
l	6
q	3

options I see are:

1. multi-column (snaking down then right) page setup with group set with an expression IIf([Amt]>=0,"APos","BNeg") then set the group header NewRowOrCol property to AfterSection

2. two subreports

3. writing records to temp table

[JoeM]

Once you have two fields, two columns is easy (the PosAmt in one column and the NegAmt in the other). That is all that different "columns" really are on Reports, they are just different "fields".

If the issue is that you do not want an alternating type pattern, where if there is a number in the Positive column, the Negative column will show a zero or blank, that you want to "move" everything up in each column to not show any blanks or zeros, then you will need to use one of the options that June provided.

[June7]

If you want data to look like:

Name	Pos	Neg
ABC	5	-1
DEF	4	-3
GHI	7	-1
JKL	2	-2
MNO	6
PQR	3

Then maybe need to do a GROUP BY (Totals) aggregate query. Use JoeM's suggested expressions to construct the two fields in query and group the data by name using Sum or Max functions to summarize the two calculated fields to single row for each Name. Or build report using Sorting & Grouping features with aggregate calcs in group header/footer section