Query criteria - Microsoft Access Forum


	Back to Access Forum Comments List

Query criteria

Robert Freeman

2 years ago

Hi All. I am formatting a printable report based on a simple query in a membership database. One of the field in the query relies on a field giving a Yes or NO answer.
The column in the report is set up showing the Yes or No answer and that works correctly.
My question is: I would like to ONLY display the YES answers. I know that is easy to do in the query BUT I need to show all the member names which is not possible using plain "Yes" criteria. Is there a way to set the criteria in the report to show 'Yes' if the query returns a 'Yes' answer BUT to display nothing if the query returns a 'No' answer? Thganks.

	Richard Rost @Reply 2 years ago
So you have: `Joe - Yes Bill - Yes Sue - No Tim - Yes` And you want to see in your report: `Joe - Yes Bill - Yes Sue Tim - Yes` Am I following correctly?

	Kevin Robertson @Reply 2 years ago
Calculated Fields IIF Function

	Robert Freeman OP @Reply 2 years ago
Richard - yes that's exactly right.

	Richard Rost @Reply 2 years ago
Kevin is right on the money. Something like: `YN: IIF(FieldValue, "Yes", "")`

	Robert Freeman OP @Reply 2 years ago
Hi both. Sorry, this should be simple but I don't understand where this should go. I've tried to figure it but am going round in circles, I've attaching a couple of images of the query and the report - can you please give me a pointer.

	Robert Freeman OP @Reply 2 years ago

	Robert Freeman OP @Reply 2 years ago

	Kevin Robertson @Reply 2 years ago
Add a Calculated Field to your query with the function Richard stated above. I have attached an example.

	Kevin Robertson @Reply 2 years ago

	Kevin Robertson @Reply 2 years ago

	Robert Freeman OP @Reply 2 years ago
Hi Kevin and thanks for your suggestion which I understand entirely. However, when I change the "IsActive" field name for "NewslettersByPost" which it needs to be I get an error message (attached). I have to admin that I don't understand what this means or how to solve it. I've also attached a screenshot of the SQL.

	Robert Freeman OP @Reply 2 years ago

	Robert Freeman OP @Reply 2 years ago

	Robert Freeman OP @Reply 2 years ago

	Kevin Robertson @Reply 2 years ago
You have the field in more than one Table in your Query. You will also need to specify the Table name so Access knows which you are referring to. Syntax: [TableName].[FieldName]

	Robert Freeman OP @Reply 2 years ago
Should this work? DisplayYesOnly: IIf([NewslettersByPostT].[NewslettersByPost],"Yes","")

	Kevin Robertson @Reply 2 years ago
Which table has the Yes/No values? From here it looks like MemberInfoVariableT `DisplayYesOnly: IIf([MemberInfoVariableT].[NewslettersByPost],"Yes","")`

	Robert Freeman OP @Reply 2 years ago
This is one of the options I have tried but this just returns 'Yes' to all the the records, whether they are Yes or No. I'll keep trying over the weekend. Thanks for your perseverance.

Robert Freeman OP @Reply

2 years ago

I've played around with this and whatever I do it will not differentiate between the Yes and No Values. The NewslettersByPostT provides the values, 1=Yes, 2=No. I tried substituting those but as expected it did not work. I tried putting the 1 and 2 values in instead of 'Yes', and that didn't work, as expected. If I change 'Yes' to 'No' in your last formula Kevin, it applies 'No' to all, the same as it applies 'Yes' to all the values if you leave it as 'Yes'.
I think it's time to give up on this problem and I'll come up with another way to achieve the result on the report.
Thanks, for your time, I really appreciate it.

	Kevin Robertson @Reply 2 years ago
What Data Type is NewslettersByPost? Is it a Yes/No field? Sounds like it's a Number field. Try this: `DisplayYesOnly: IIf([MemberInfoVariableT].[NewslettersByPost=1],"Yes","")`

	Robert Freeman OP @Reply 2 years ago
I've already tried that I think - or similar- it just asks for the parameter value. See image.

	Robert Freeman OP @Reply 2 years ago

	Robert Freeman OP @Reply 2 years ago
Hi Kevin. I've been playing around based on your last idea - Bingo! Here's what worked: DisplayYesOnly: IIf([MemberInfoVariableT].[NewslettersByPost]=1,"Yes","") Thank you so much for your help. It's really appreciated.

	Kevin Robertson @Reply 2 years ago
Oh. I made a typo. Oops. Glad you got it working.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen