Can Anyone Answer this Simple Query Question?

[CassandraB]

I can't seem to get this question answered..... so here goes ............
If I have a query, and If I have two seperate fields unrelated to each other in the same table and I want to select all the records that populate one field with 'Fail' and select all the records in the other field with 'Yes' (NOT the records where both of the seperate fields have a 'Fail' and a 'Yes' in common), how would I query it? What would I have to put in the criteria portion of my query? Thanks a bunch.

 

[AccessAmateur]

Use the criteia build wizard to create the criteria:

TableName!FieldName = Yes OR TableName!FieldName = Fail

 

[Robert Dunstan]

Ok you need to create 2 queries.

In query 1 set the criteria to 'Fail' (to select all records with Fail in it) and in the 'Yes' field set it to Is Null (to ignore records with Yes in it)
The result should be to only select the records that have Fail and not 'Yes'

In query 2 do the exact opposite i.e. Is Null for 'Fail' etc.

Then you can create a 3rd query and base it on these 2 to get the required information

Hopefully I've understood what you wanted and this helps

Rob

 

[CassandraB]

Rob, the "IsNull" doesn't seem to be working, there aren't any resurlts when it is used (in both queries). There are many pass/fail's and yes/no's that populate the 2 fields. How would I do the 3rd query and would I use that query when creating my report? Thanks.

 

[The_Doc_Man]

You can do this from the query design grid.

In the first row (field), name the items that you want to see. Place a check in the SHOW row for each of those items. Any that could be null, enclose with the NZ() function, as Nz([MyField1],"")

OK, next, let's look at the criteria rows. EACH ROW is a logical OR contributor. So in the first row, for the item that has to be yes, place criterion =True (Yes/No fields that are Yes are also TRUE).

In the second row of criteria, for the item that has to be "FAIL", place criterion ="fail" (I am assuming that you have a string here.)

That should handle it.

 

[Robert Dunstan]

CassandraB

Here's an example of what I played around with (it's in 2000)

Am I anywhere near the mark of what you wanted?