Suppress blank rows in report (1 Viewer)

ponneri

Registered User.
Local time
Today, 21:56
Joined
Jul 8, 2008
Messages
102
Hi.

How do I suppress (not display) the empty fields in detail section of my report ? Table structure is as:

Order_id, Order_Value, Tax1_Amt, Tax2_Amt, Tax3_Amt

Report format is as:

Ord_id : Ord1001 Ord_val : 500000
Tax1_Amt : 12000
Tax2_Amt : 3650

Ord_id : Ord1003 Ord_val : 965000
Tax1_Amt : 1000
Tax3_Amt : 750

This is because, on some orders customer may have paid Tax1+Tax2, on some other order Tax1+Tax3 but not Tax2. In another order customer may have paid all three taxes.

So, I have to make sure I show only the Tax number paid and suppress the unpaid (empty) tax field in the detail section of my report for each order id.

Please help.
 

plog

Banishment Pending
Local time
Today, 11:26
Joined
May 11, 2011
Messages
11,611
My gut is telling me you haven't set up your tables correctly. If done correctly, there would be no record for any tax they hadn't paid.

Do you have fields called something like Tax1, Tax2, etc.? If so, that's wrong. You shouldn't numerate field names. Instead you need a new table to put that data into properly. Like so:

TaxesPaid
Ord_Id, TaxNumber, TaxAmount
1001, 1, 12000
1001, 2, 3650
1003, 1, 1000
1003, 3, 750

With a table structure like that, your report would display correctly.
 

ponneri

Registered User.
Local time
Today, 21:56
Joined
Jul 8, 2008
Messages
102
You are right !

But this table with Order_id, Order_Value, Tax1_Amt, Tax2_Amt, Tax3_Amt......and several other fields is drawn into Access from an Excel worksheet sent to us.

And in Excel it's a strip of information in one row per order where the customer (depending on some condition) may have paid one, two or all three taxes.

In the Access report, however, I need to show it in the way I described earlier.
 

plog

Banishment Pending
Local time
Today, 11:26
Joined
May 11, 2011
Messages
11,611
Then you need to either move that data to a correctly structured table, or implement a query that acts as one.

A UNION query could do that for you like so:

Code:
SELECT Order_Id, Tax1_amt AS TaxAmount, 1 AS TaxNumber FROM YourTable
UNION ALL
SELECT Order_Id, Tax2_amt AS TaxAmount, 2 AS TaxNumber FROM YourTable
UNION ALL
...

Then you would use that query to build another query to base your report off of.
 

Mark_

Longboard on the internet
Local time
Today, 09:26
Joined
Sep 12, 2017
Messages
2,111
For the tax amounts, is there a specific reason they are broken out? I know that in some areas local regulations require different taxes to be paid and reported separately. Do you also have to show a "Total tax amount" and "Total paid"?

If so, you may be able to use something like IIF(Tax1_Amt <> 0, Tax1_Amt, "") and potentially format the amount, if needed.
 

ponneri

Registered User.
Local time
Today, 21:56
Joined
Jul 8, 2008
Messages
102
Yes, there are 3 different types of taxes applicable. Depending on the type of order one or more taxes will be paid by the customer.

And I may have to show the "Total tax amount" and "Total paid" also.

Thanks for the help.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:26
Joined
Feb 19, 2002
Messages
42,970
There is no need to restructure the query.

As long as each tax is on a separate row, you can get Access to completely hide the row by using the Can Shrink/Can Grow options. When a field is NULL and it is the only one on a row, Access will suppress the entire row if Can Grow/Shrink are set to Yes.

However, if the tax fields contain 0 rather than null, that is a value and Access will not suppress printing. You would need to add code to the On Format event to hide the control and the label. OR, you could modify the query to change 0 to null. Just keep in mind that if you change a tax to null, you will have to change your calculation that sums the taxes to use the Nz() function.
 

ponneri

Registered User.
Local time
Today, 21:56
Joined
Jul 8, 2008
Messages
102
Great !

I was thinking of that option (can grow/shrink) but was not sure if the row would get hidden.

Thanks a lot !! Will try it out @ work.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:26
Joined
May 7, 2009
Messages
19,169
Here is your sample Report
that hides with 0 or null tax.
 

Attachments

  • sampleTaxReport.zip
    40 KB · Views: 193

Users who are viewing this thread

Top Bottom