0 values in reports

[Guest]

I have a fairly simple question, logically the answer is straightforward, but
I am not an Access wiz so the solution eludes me. I have an inventory control
database of the following format:
I have a range of products (ItemCode) of which we make many batches
(BatchNumber). I then perform transactions on each batch (for example we make
400 units of ItemCode Y, BatchNumber Z, and then later in the week we
despatch 200 units of the same BatchNumber Z of ItemCode Y. We then despatch
another 100 units of the same BatchNumber Z of ItemCode Y the following week,
etc.)
I have a report which shows each batch of each product and the number of
units currently on hand. The number of units on hand is calculated from the
difference between the sum of UnitsProduced and the sum of the
UnitsDespatched. When the number of units on hand gets to 0, I do not want
the batch to be displayed on the report, I only want the report to show
batches where there are units on hand.
Logically I need: If Units on Hand > 0 then display the batch number and
number of units on hand in the report. My report is grouped by products
(ItemCode), so I get a report with the products as the first heading, with
the batch numbers and units on hand displayed for each product.
I would be happy to share my code.
Any help would be appreciated
Andrew

 

[Guest]

Make a query which will be the record source for the report.
In the query filter the records.
In the Criteria cell of the "The Number of Units" column put: <>0
The query will return only records where the number of units is not zero.

 

[Marshall Barton]

I have a fairly simple question, logically the answer is straightforward, but
I am not an Access wiz so the solution eludes me. I have an inventory control
database of the following format:
I have a range of products (ItemCode) of which we make many batches
(BatchNumber). I then perform transactions on each batch (for example we make
400 units of ItemCode Y, BatchNumber Z, and then later in the week we
despatch 200 units of the same BatchNumber Z of ItemCode Y. We then despatch
another 100 units of the same BatchNumber Z of ItemCode Y the following week,
etc.)
I have a report which shows each batch of each product and the number of
units currently on hand. The number of units on hand is calculated from the
difference between the sum of UnitsProduced and the sum of the
UnitsDespatched. When the number of units on hand gets to 0, I do not want
the batch to be displayed on the report, I only want the report to show
batches where there are units on hand.
Logically I need: If Units on Hand > 0 then display the batch number and
number of units on hand in the report. My report is grouped by products
(ItemCode), so I get a report with the products as the first heading, with
the batch numbers and units on hand displayed for each product.
I would be happy to share my code.

Put a text box with the units on hand calulation in the
batch header section. Then check it in the section's Format
event and make the sections invisible:

Me.Section(5).Visible = Me.txtOnHand > 0) 'group hdr
Me.Section(0).Visible = Me.txtOnHand > 0) 'detail

 

[Guest]

I have the units on hand calculation in a text box in the batch header
section.
What does the rest of your response mean?
Thanks for your help
Andrew

 

[Marshall Barton]

Those two lines are VBA statements that you need to place in
the group header section's Format event procedure.

Their purpose is to make the group header and the group
detilas invisible when the total is zero.

 

[Guest]

Marshall, you did'nt give too much detail, but I used what you gave and
managed to work through it. It solved an annoying problem I had with my
monthly debtor account statements, where the Balance calculated field
reflected zero balances, which of course I did'nt want in the statement. You
are brilliant, and thankyou.