Returning Null or all records in query (1 Viewer)

[fredje]

Depending on the answer of a question, i would like to get the records that have no data in that specific field, otherwise i want the query to give me all records.
I've tried this in "criteria" but it doesn't work: IIf([Question? J/N]="N";"Is Null";"")

Who can help me?

Thanks
Fred

 

[RV]

Use this WHERE clause:

WHERE columnname LIKE IIF([Question J/N]="N",Null, "*")

Could be you'd have to replace the comma's by semicolons

RV

 

[fredje]

RV thank you for your responce.

Your solution works for 50%.
The part with the "Null" doesn't work.
Answering the question with "J" returns all records. "N" returns no records at all.
I've tried Null, Is Null and I typed them with and without quotation marks.

Did I miss something?

Fred

 

[FlyerMike]

Have you tried testing the expression Nz([Question J/N],"Null")?

I'd run the query (without a WHERE clause) using this item and check the unique values associated with it.

 

[RV]

The part with the "Null" doesn't work.
Answering the question with "J" returns all records. "N" returns no records at all

In your original thread, your code implies that you want to retrieve all records having a specific field being nulll if the question is answered by N.
Otherwise, all records should be retrieved.

Now if you do not retieve any records, logically that would be 'cause you have no rows whereas the field is null.
Code should work OK, tested it, works for me.

Otherwise, post your full query.

RV

 

[Jon K]

Depending on the answer of a question, i would like to get the records that have no data in that specific field, otherwise i want the query to give me all records.

I've tried this in "criteria" but it doesn't work: IIf([Question? J/N]="N";"Is Null";"")

Put this in a new column in the query grid, replacing with the correct field name:-

---------------------------
Field: IIf([Question? J/N]="N"; [FieldName] Is Null; True)

Show: uncheck

Criteria: True
---------------------------


Now when you answer N, only the Null records will be returned.
Any other answers will return all the records i.e. including the Null ones.
.