Access query and form

Greetings -

I have a Access database that I run a report from. There's 4 yes\no fields that the query looks at to generate the report.

Breakdown:
- Access database contains clients
- clients have four items that can be checked (Yes\No)
- Query checks each client and see if they have any checks in those four items
- Form displays the results

My need is to NOT show the any clients on the form that have No for all four items.

I have tried to play around with the Query Criteria "*", Yes Or No and so forth. My knowledge with expressions and functions is minimum. Please let me know what you need to see from the mdb to help.

Thanks
Form-example.png
ShapiroSherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
Try this:

SELECT * FROM YourTable
WHERE HC + AN + EC + Gift = 0

Open in new window

0
Jeffrey CoachmanMIS LiasonCommented:
Try something like this perhaps:

WHERE YesNoField1<>False AND YesNoField2<>False AND YesNoField3<>False AND YesNoField4<>False
0
mbizupCommented:
Oops -- got the logic backwards.  This is to exclude items where all are false:


SELECT * FROM YourTable
WHERE (HC + AN + EC + Gift) <> 0

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jeffrey CoachmanMIS LiasonCommented:
Just refreshed...
mbizup's will be shorter to type in,  and will probably execute faster
;-)

Jeff
0
Jeffrey CoachmanMIS LiasonCommented:
...and it seems that mbizup actually took the time to look at your attachment...
;-)

Jeff
0
mbizupCommented:
Lol!
0
Dale FyeCommented:
But "None" does not necessarily indicate a Yes/No field.  Is that field value being converted from No to None in the query?
0
mbizupCommented:
That attachment actually shows GiftType as a text field ("none") versus "No"... so I'm assuming your table has a related fourth yes/no field.

If you actually need to exclude "none" (text)...

SELECT * FROM YourTable
WHERE (HC + AN + EC) <> 0 OR GiftType <> "None"
0
Dale FyeCommented:
or you could use:

WHERE [HC] OR [AN] OR [EC] OR [Gift]
0
Jeffrey CoachmanMIS LiasonCommented:
Ah, so it does pay to look at the attachments...

So then this is incorrect from the original post
There's 4 yes\no fields
0
mbizupCommented:
<< So then this is incorrect from the original post >>

Not necessarily incorrect... but definitely a little confusing.  :-)  There could be a T/F "GiftPurchased" column in addition to the GiftType in the table or query.
0
ShapiroSherAuthor Commented:
'None" is generated from a Yes\No field. One of the 4.
0
mbizupCommented:
OK - then assuming that the fourth y/n field is indeed included in your query, then we should have you covered in a few of the posts here.
0
ShapiroSherAuthor Commented:
Do I Select * in the query that's generating the report?

"I'm a Newb to Access"
0
mbizupCommented:
That is just for example's sake.

What is the rowsource property of your report (looking at it in design view)?

If the rowsource points to a query, post the SQL view of the query here.
0
ShapiroSherAuthor Commented:
SELECT ClientDatabase.ID, ClientDatabase.Prefix, ClientDatabase.LName, ClientDatabase.FName, ClientDatabase.MName, ClientDatabase.Suffix, ClientDatabase.Title, ClientDatabase.Company, ClientDatabase.Add1, ClientDatabase.Add2, ClientDatabase.Add3, ClientDatabase.City, ClientDatabase.State, ClientDatabase.Zip, ClientDatabase.Country, ClientDatabase.Attorney, ClientDatabase.HolidayCard, ClientDatabase.Gift, ClientDatabase.GiftCode, ClientDatabase.Announcement, ClientDatabase.ECard, ClientDatabase.Notes, ClientDatabase.EditLog, ClientDatabase.PrintLabel, ClientDatabase.Email
FROM ClientDatabase
WHERE (((ClientDatabase.Attorney) Like [Forms]![SearchTerm]![SearchTerm] & "*"))
ORDER BY ClientDatabase.LName, ClientDatabase.FName;
0
mbizupCommented:
Try this... I'm "aliasing" your table name as "c" to shorten your query's text a bit (see the FROM clause):


SELECT c.ID, c.Prefix, c.LName, c.FName, c.MName, c.Suffix, c.Title, c.Company, c.Add1, c.Add2, c.Add3, c.City, c.State, c.Zip, c.Country, c.Attorney, c.HolidayCard, c.Gift, c.GiftCode, c.Announcement, c.ECard, c.Notes, c.EditLog, c.PrintLabel, c.Email
FROM ClientDatabase  c
WHERE (c.Attorney Like [Forms]![SearchTerm]![SearchTerm] & "*") AND 
(c.HolidayCard + c.Announcement + c.ECard + c.Gift) <> 0
ORDER BY c.LName, c.FName;

Open in new window


Double check the field names I've used in the WHERE clause (I'm guessing at which ones your Y/N fields are) and correct them if needed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShapiroSherAuthor Commented:
Very quick and precise! mbizup's Solution was my Resolution.
0
ShapiroSherAuthor Commented:
Thanks to everyone for your help!
0
mbizupCommented:
Glad to help :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.