To hide empty/null fields on reports.... (1 Viewer)

[ciccio83]

Looking at the image. Someone knows how it is possible to hide (or remove) empty fields for each record? Don't care about graphic and style, it is only an example....that report is a collection of articles->working orders (odl) and realtives shoes numbers (from 28 to 47). Under the shoes numbers there are the pair quantity of that article...i need to hide the column in which, under the field relative at the shoe number, there aren't quantities.

 

[Scooterbug]

I would filter out the blank entries on the query you are using for the report. By putting Is Not Null in the criteria field for the pair quantity they wont show up on the report.

 

[ciccio83]

Thank you for your reply, but it doesn't work. If i put on the criteria fields the "Is Not Null" condition and the first field is ever null for every record, the query don't show any record on screen.

 

[Scooterbug]

On the example you posted, are each one of the shoe numbers a separate record? or is all of the data shown on one record?

 

[ciccio83]

Only on one record....the shoe numbers fields are not separated, all of them are inclused in one table....

 

[Scooterbug]

Well, off the top of my head, the only way I can think of is to have the report check to see if there is a value for each entry and if not, have it set the visible property of each field and label to False.

On a side note, is there a reason why all of that data is on one record?

 

[ciccio83]

Try to see the image attached on the first post...have you an idea of how i can structure the database for have the thing i need?

ODL->ART->NUMBERING

 

[gemma-the-husky]

there is a difference between suppressing rows where a particular field is blank, and including all the rows, but suppressing the data for blank fields.

aren't the fields suppressed anyway for the blank fields - or do you have a visible border that you want to hide perhaps.

if so, look at the FORMAT event for the DETAIL section of the report. - then for each field you wil have to say

field1.visible = not isnull(field1)

---------
note that you may get an issue using field1 in an event FOR field1 - access may not do this properly

 

[Scooterbug]

Well, looking at the image, I would have two tables. One for the order, one for a line item.

OrderTable:
OrderID
(Any other order info)

OrderDetail:
OrderDetailID (AutoNumber, Primary Key)
OrderID (foreign key)
ShoeNumber
Quantity

From there, you can create a query that only shows the shoe number and quantity

 

[ciccio83]

You've not put on article's name and referred odl...it's not so simple...

 

[Scooterbug]

Ok, just looked it over and re-read your first post. ODL = OrderNumber, ArticleName (Highlight or Lofu). Assuming that is correct, it would look like this:

tblOrders:
OrderID: Autonumber (PK)
odlID

tblLineItem:
LineItemID (auto, PK)
odlID
Style
size
quantity

I have attached a small sample database to show you how I would set it up. I tend to be more of a hands on explainer when it comes to this stuff.

 

[ciccio83]

Ok, this is a way to do what i want but the question now is this: how can i transform this:

size quantity
40     100
42     200
44     300

in

size         40    42    44
quantity    100   200   300

I NEED this format...and this is the real problem.....have you understand now why i've done that strange structure for my database before?

 

[Scooterbug]

After playing around, I got the general format for ya. It's not exact to what you had shown in your screen shot, but it's close.

Basically, you have to setup the page to print in columns. I've attached an updated database with the report setup to print with columns. Hope it helps. You can play around with it to get it to your liking, but the general format is there for ya.