Need a query to find records that contains "No"

[rehanemis]

Hi,

I have a table 45 fields. There is a field ID. I would like to get the records where fields contains "No". I would not like to create 45 queries for each field check.

Can anyone suggest how can i generate a table which shows ID, Field Name that contains value "No"

Thanks

Rehan

 

[JHB]

You can use Or in your query, something like below.

WHERE [Field1]='No' Or [Field2]='No' Or ...

 

[jdraw]

You may find this of interest.

45 fields in 1 table is quite rare --not unheard of, but may need some review. You mention 1 table but haven't indicated if there are others.

Good luck.

 

[marlan]

VBA?

dim strSQL as String, rst as Dao.RecordSet, fld as Dao.Field

StrSQL = "SELECT * FROM tblName WHERE "
Set rst = CurrentDB.OpenRecordSet("tblName")
for each fld in rst.Fields
  strSQL = strSQL & "[" & fld.Name & "] = 'No' Or "
Next FLD
strSQL = Left(strSQL, Len(strSQL )-3)' remove last "Or "

If you want No as part of the text, look into 'Like': http://www.techonthenet.com/access/queries/like.php

you can put this SQL in a Query: http://www.access-programmers.co.uk/forums/showthread.php?t=204101

ATB!

 

[Galaxiom]

If you are in control of the structure you need to reconsider it. These fields would be better held as separate records in a related table with another field to record a code for what it is that is currently recorded as the field name.

Then the query becomes very simple. Assuming you are looking for a "No" string in the text.

SELECT ID, codefield WHERE somefield LIKE "*No*"

If you are just recording "Yes" or "No" you should consider changing to a Boolean field.