Help on creating adjustable/expandable report

[cbende2]

Hello all,

I'm trying to create a report.

In an ideal world this report would go through each record, and spit back out the ID of the record and also every field in that record that is blank, null, or 0.

So for example, if I have 3 records(A,B,C), and 5 fields(field1,field2,field3,field4,field5)

The report would look something like this:

Name: A
Field1: Blank
Field4: Blank

Name: B
Field2: Blank


Field3: Blank
Field5: 0

Name: C
Field2: Blank
Field5: 0

I have no idea how to go about doing this or even if this is possible to create a report like this.

Any help, information, or ideas would be greatly appreciated!

[June7]

I don't understand. Why does each record have each of the specified fields? Why are fields blank, null, or 0? Is there no other data?

[cbende2]

Well in my actual table there are about 40 fields. And the reason I'm doing this is to be able to list each record and only the fields associated with that record that need to be filled out.

[June7]

But why blank, null, 0?

40 fields that don't require data in every field - that's a lot. Doesn't sound like a normalized data structure.

[cbende2]

Well basically we are tracking a process.

For example, one field is named DateLetterMailed, and another is DateLetterReturned. Well, DateLetterMailed will be filled out first, and once we get the letter back, we will then fill in that date for DateLetterReturned. And thats just an example..

And not every record is in the same step of the process, that's why I'd like for it to display only the fields of the records that are blank, null, or 0.

[June7]

One technique is to set textbox height to 0 then position textboxes just a bare fraction below each other and only fields with data will grow. All textboxes will still occupy space but maybe not so noticeable. This will work for a few (maybe 4) but I doubt 40. And you want only the fields that are empty so would have to do a calculation that reverses the values and returns some alternative for the blank/Null/0, like "Not Entered Yet" and everthing else is set to Null.

Another approach would use a UNION query to rearrange the fields vertically. Use the UNION as report RecordSource and apply filter to report when opening to exclude records with data.

SELECT ID, "Returned" AS Source, [DateLetterReturned] AS Data FROM tablename
UN ION SELECT ID, "Mailed", [DateLetterMailed] FROM tablename
....;

There is a limit of 50 SELECT lines. There is no wizard for UNION, must type into SQL View of query builder.