Prev: Pageheader in a multi columns
Next: Grouping
From: SteveL5231 on 24 Jan 2010 10:51
I have a report which lists records sorted by part number and then by date.
Also on the report for each record is an inventory qty. So the first record
may show part #1 due 1/31/2010 and display an inventory qty of "5". If the
next record on the report is for the same part number, regardless of the due
date, I want the inventory qty to display "0". I realize this is a little
strange but it has to be displayed this way. I can't just hide dupes because
the next record for an entirely different part number may need to show the
accurate inventory qty.

To further explain...

The report now looks like:

Part # Due Date FG Qty
12345 1/31/2010 1
12345 2/10/2010 1

Any I want it to look like:

Part # Due Date FG Qty
12345 1/31/2010 1
12345 2/10/2010 0

Note the "0" because the FG Qty is already shown in the first record which
is for the same part number. In other words, if the part number is the same,
replace whatever qty is in FG Qty with a "0".



From: John Spencer on 24 Jan 2010 11:38
If you group on Part # you could
Add a new control to the detail section
== Name: txtLineCount
== Set its source to
=1
== Set its Running Sum to Over Group

Change the control for Inventory qty
== Set its source to
== Abs([FG Qty] * [txtLineCount]=1)
(Make sure your control has a name that is not the name of a field) - Just add
"txt" to the name of the existing control.

That should print the value of FG Qty on the first line of the group and then
zero for all remaining lines.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

SteveL5231 wrote:
> I have a report which lists records sorted by part number and then by date.
> Also on the report for each record is an inventory qty. So the first record
> may show part #1 due 1/31/2010 and display an inventory qty of "5". If the
> next record on the report is for the same part number, regardless of the due
> date, I want the inventory qty to display "0". I realize this is a little
> strange but it has to be displayed this way. I can't just hide dupes because
> the next record for an entirely different part number may need to show the
> accurate inventory qty.
>
> To further explain...
>
> The report now looks like:
>
> Part # Due Date FG Qty
> 12345 1/31/2010 1
> 12345 2/10/2010 1
>
> Any I want it to look like:
>
> Part # Due Date FG Qty
> 12345 1/31/2010 1
> 12345 2/10/2010 0
>
> Note the "0" because the FG Qty is already shown in the first record which
> is for the same part number. In other words, if the part number is the same,
> replace whatever qty is in FG Qty with a "0".
>
>
>
 | 
Pages: 1
Prev: Pageheader in a multi columns
Next: Grouping