Report presentation issue

[astath]

Hello,


I'm trying to create a specific report in order to produce an invoice. The format of the invoice needed requires multiple fields per item on one column (each entry has 5 columns; only the first has multiple fields to it) so I have been able to reproduce that. The problem is that for a specific item only one field is needed. Currently if there are no info on the remaining fields the item isn't added to the report. Is there a way to hide the fields not existing for this item only for that item?

Many thanks.

[ranman256]

access default report setting, if you have multiple items, they list down as default.
now a subreport CAN display items left-to-right THEN down. You have to set it in
Report Page Setup,
Columns,
down then across

[astath]

Thanks for the reply.

Since it's my first time working with Access on a complex project and in order to be on the safe side, this is what I have created so far.



The item in question has only a itemdescription field. So the logic is that for that specific entry all other fields won't be present.
I have never used subreports so far so if you could help me a bit by pointing me to the right direction I would realy appreciate it.

[Gicu]

Read this (somewhat) related thread and see if it helps you: https://www.access-programmers.co.uk...ection.311013/
I believe what you want is to hide the labels for the MAKER, TYPE,P/N all the way through Condition as the associated text boxes will already be empty. You could replace the label controls with textboxes and apply conditional formatting to change the font color to white if the fields are empty or you could build the logic into the new textboxes control source properties: =IIF([HideControls]="Yes","","MAKER").
The HideControls is a new calculated field in the report's record source query that shows "Yes" or "No" based on whatever logic you use to identify that specific item.

If you get stuck create a small sample with just the report and the related objects (tables, queries) with a coulpe dummy records and upload it here.

Cheers,
Vlad

[astath]

After reading the suggested topic I can admit that i failed miserably. I'm uploading a small version of the DB to test the waters.

Thanks in advance!

[Gicu]

Have a look at the updated report. I added a calculated field to its recordsource then changed the labels to text boxes. I have a feeling you will have to change the design of your query that feeds this report to make it work (change some joins from equi joins to outer joins) but can't say for sure with no meaningful data.

Cheers,
Vlad

[astath]

Thank you very much for your time and effort over this. Actually the only problem with this scenario is that access doesn't let me reference a different field's value at this stage. If you visit from the Navigation Form the Doc Creation and choose On exchange basis you will have some data to see what happens. What I'm trying to do is to hide the remaining fields when the value of itemdescription is "packing". I tried to edit the control on your version but the field value gets disregarded.

[Gicu]

Sorry but I don't get it, the packing item doesn't make it in the report because it does not have related entries in Condition, Usage, Availability and WH tables and the query uses equi joins as I mentioned in my previous post. I have changed the reports record source to use outer joins for those tables and now it does show. But I don't get how you want the final report to look, can you maybe post a picture on what is the desired result.

Cheers,
Vlad

[astath]

I'm pretty sure that i'm failing to explain what I'm trying to accomplish by writting.
The ideal achivement would be (if it's at all possible) to have something like this:



That is, for specific entries that don't have values to use with fields like Condition, availability etc. to totally remove the fields that dont have a value and resize accordingly. If that's impossible due to limitations of Access then the next best thing would be something like this:




Hope the visual representation explains better the question than I could. As for the JOIN opperations, thanks for helping out. As you can imagine I used the create report wizard and Access created them for me (talk about novice user!) It was getting the job done though aat the time so I didn't check further. Most probably you saved me from posting another question when I reached the point that this would create issues.

Again thanks for your help!

[Gicu]

Please have a look at this version, this is as far as I can get it.

Cheers,

[astath]

You are a miracle worker! So apparently the only thing that Access is unable to do is to create the appropriate borders for each part, right?

[Gicu]

You need to look at it in print preview mode and you will see the borders (not actually borders as they are lines drawn during the Print event of the Details section of the report). I have set the actual borders of the controls to Transparent so they don't interfere with the code.

Cheers,
Vlad

[astath]

I didn not see that coming! Can't thank you enough for helping out! I'll try to understand how you did it since I have a bunch of same reports that I will need to create. Thank you for your time and effort over this!

[Gicu]

Glad to help, post back if you get stuck.

Cheers,

[astath]

Good afternoon! May i bother you a bit more regarding the report generation? I have found the code you created along with the tags in the specific fields. What I can't find is how do you set the tag value depending on the item selected. Is it possible to assist me in finding out?