CODEWITHSUNDEEP
ms-accessvbams-access-2016
Tazit
Tazit

Reputation: 3

Access report to leave out "no data" fields without spaces

I have 20 "Numeric" fields. They have input of 1 if Positive, 0 if Negative, and left blank (null) if not valid for this particular record. I am trying to create a report that will return each field on it's own line stating "Negative Orange", or Positive Orange", or leave Orange out of the report completely (and continue on with the remaining 19 fields depending on what each input is) without spaces in between lines if the particular field is in fact blank (null). This is an example of what I need in one column of the detail section of my report: 

Negative        CBC  
Negative        Vitamin D  
Negative        Vitamin E  
Negative        Vitamin A  
Positive        Vitamin B-12

There are 20 possible substances that could potentially be tested. Not every record tests for each substance. I would like the report to only show what has been tested for, leaving no blank spaces in the final report.

Upvotes: 0

Views: 254

Answers (1)

June7
June7

Reputation: 21380

You did not provide example of raw data and actual names so substitute as appropriate.

SELECT ID, Field1 AS Data, "Test1" AS TestName FROM tablename
UNION SELECT ID, Field2, "Test2" FROM tablename
. . .
UNION SELECT ID, Field20, "Test20" FROM tablename;

Include as many fields as you want.

The constructed field Data can be a calculation in each SELECT row to convert number to text. Since you don't seem to use a Yes/No field:

IIf([Field1]=0, "Negative", IIf([Field1]=1, "Positive", Null)) + " Orange"

Or in textbox on report using UNION query as RecordSource:

IIf([Data]=0, "Negative", IIf([Data]=1, "Positive", Null)) & " Orange"

Use the UNION query as RecordSource for report and apply filter to remove null records.

SELECT * FROM UnionQuery WHERE NOT Data IS NULL;

Upvotes: 1

Related Questions