Same query in multiple fields (1 Viewer)

Brewsi · Mar 9, 2016

Hi there,
This seems like it should be a simple question bit for the life of me can't figure it out.

I have multiple fields in a table.
I want to run a query that says

If any field reads "no" then show record in query,

I am not proficient in SQL bit am using the query tool. I have put "no" in all of the Or criteria but it brings me back no data

Can anyone help please

Thank you in advance

arnelgp · Mar 9, 2016

on query design, add another calculated field:

Instr("" & [field1] & [field2] & [field3] & [fieldNth], "no")
Criteria: > 0

plog · Mar 9, 2016

In design view, you would stagger your criteiria. That means for the first field you would put 'No' on the first criteria line, for the second field you would put 'No' on the second criteria line, third field--third criteria line, etc.

Brewsi · Mar 9, 2016

Hi there

Thanks for your replies

I have staggered the Or option like you said but I have run out of lines. Is there any way to increase the amount of ORs?

plog · Mar 9, 2016

You could go into the SQL and repeat the patter in the WHERE clause. However, arnelgp's advice might be better.

The_Doc_Man · Mar 9, 2016

If you have that many items, I wonder if your problem is an original design issue. That question and your responses to the replies make me wonder if you are looking at a case of a "repeating field" where you should really have a parent/child relationship. The individual yes/no answers would be recorded in child records associated with of the parent record. Then you could use a

Code:

DCount("[Answ]","childtable","([Answ]=False) AND ([QuestionID]=" & cstr(Qnumber) & ")" )

Brewsi · Mar 10, 2016

I think you made be right in as much as the design of the db is incorrect. I am quite new to this and still making mistakes

arnglgp does that code allow for unlimited fields?
DOCMAN do you know of any good material to guide through db structure. As I have a lot of data to capture under one record.

arnelgp · Mar 10, 2016

sir, you have to try it with whatever field you have with "no" value.

Brewsi · Mar 10, 2016

Thanks for your help

all sorted now

Same query in multiple fields (1 Viewer)

Brewsi

New member

arnelgp

..forever waiting... waiting for jellybean!

plog

Banishment Pending

Brewsi

New member

plog

Banishment Pending

The_Doc_Man

Immoderate Moderator

Brewsi

New member

arnelgp

..forever waiting... waiting for jellybean!

Brewsi

New member

Similar threads

Users who are viewing this thread