SQL - Eleminate null fields from a report

[roles40]

Using MS Access 2k, Query builder, SQL View.

I have a table that has about 100 fields, all but four of these are boolean(Yes/No) data types. I am attempting to generate a report that will only display the fields that are set to YES. Here is what I have tried to no avail.

SELECT * FROM MyTbl
WHERE MyTbl.* != 0
SORT BY $/Primary Key/$

I tried taking this query and putting that into a report, but suprise suprise, it didn't work. Any suggestions/help would be greatly appreciated.

Thanks in advance!!

JR

 

[mwolf00]

SELECT * from myTable where yn1 = "Yes" and yn2 = "Yes" and yn3 = "Yes" and yn4 = "Yes"

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[roles40]

mwolf,
Thanks for the quick response, but I think that we got some wires crossed. The table consists of 4 text fields, and about 96 Y/N fields, that would be a very long command. Any way to shorten that up?

Thanks again!!

JR

 

[MisterC]

Access treats YES as -1 and NO as 0. If you did a query with a calculated field (SUM of fields with the yes/no values) you could use it as the criteria.

SELECT * from Table Where CalculatedField > 0

 

[MisterC]

Wait, after re-reading your question I think you want something different. You just want a report to list the "YES" answers for each group of text fields, right?

The previous suggestion I listed would give you a recordset where at least 1 item was marked yes. I think you want a report to list out just the YES answers.

If that is the case, you can use the same query, but in the Reports Detail_Format Code, loop through the YesNo fields on the report and if they are "NO" then turn them invisible.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    'If value is NO (0) then field will be invisible
    'If value is YES (-1) then field will be visible
    Me.YesNoField.Visible = Me.YesNoField.Value
End Sub

I think that would work, but there may be an easier way.