Solved

Y/N field Report in Access 2010

Posted on 2012-04-07
15
586 Views
Last Modified: 2012-04-17
My query has CustID, CustName, ServiceDate, and CarWash.

CarWash is a y/n field
CarWash Cost (Currency field)

Not all customers want a car wash, but my report shows all customers regardless if Car Wash is checked or not.

How can I make my report show only those CarWash Fields which = yes?

I've tried; Yes/no, Y,  1, True (QBE Criteria)
Also tried ; CarWash Cost >1 (QBE criteria)

But no luck

Thanks much,

Lucy
0
Comment
Question by:lucyLuna
  • 8
  • 3
  • 3
15 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 37819719
Your query should look something like this:


SELECT YourTable.[CustID], YourTable.[CustName], YourTable.[ServiceDate], YourTable.[CarWash]
FROM YourTable
WHERE (((YourTable.[CarWash])=Yes));

Flyster
0
 

Author Comment

by:lucyLuna
ID: 37819727
Hi FLyster, the WHERE Statement goes in the Criteria field of the query?

Thanks much!
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37819783
lucyluna,

here is some123 QBE view of the query
0
 

Author Comment

by:lucyLuna
ID: 37819785
Flyster, I figured out...and your solution works works great.

 *********but how do I add more selections, for example:

CarWash, WindowRepair, CarDetail...etc...I have six fields do I add each on the WHERE Clause? separated by a comma?***********

I am accepting your solution

Thanks so much!

Lucy
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
ID: 37819806
to add more criteria, you will add the field in the where clause  as posted by Flyster using either AND or  OR

WHERE (((tblCustomers.CarWash)=True) AND ((tblCustomers.WindowRepair)=True) AND ((tblCustomers.CarDetail)=True));

or Using OR

WHERE (((tblCustomers.CarWash)=True)) OR (((tblCustomers.WindowRepair)=True)) OR (((tblCustomers.CarDetail)=True));
0
 

Author Comment

by:lucyLuna
ID: 37819810
@Capricorn1, I tried that prior to asking my question...and those options did not work...Flyster's solution worked, so I am accepting it.

Thanks for jumping in...I need all the help I can get :)


Thanks again,

Lucy
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37819819
Lucy,
If any of the queries i posted did not work, and you are sure that the field is Yes/No Type
Your db have a big problem..

Try doing a compact and repair and also check if you have missing references in your VBA module
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:lucyLuna
ID: 37819868
Capricorn1, The fields are y/n type, and the report is now pulling fine...the only thing I need to do is add the additional fields as you suggested using the AND/OR in SQL window of the query....I am working on this now...I am getting a syntax errro...probably misspelled something...I am checking.

If all else fails, I will do as you suggest, compact repair...I don't see any missing references in VBA.

Thanks a bunch :)

Lucy
0
 
LVL 22

Accepted Solution

by:
Flyster earned 250 total points
ID: 37819907
Sorry Lucy, I'm at work now. Capricorn1 solution should work with the OR placed within the Where statement. It should look like this:

SELECT YourTable.CustID, YourTable.CustName, YourTable.ServiceDate, YourTable.CarWash, YourTable.WindowRepair, YourTable.CarDetail
FROM YourTable
WHERE (((YourTable.CarWash)=Yes)) OR (((YourTable.WindowRepair)=Yes)) OR (((YourTable.CarDetail)=Yes));

If you don't use "OR", you will only get records where all your Yes/No fields are True. I'll try to check back in a bit, but Capricorn1 is far more knowledgeable in this subject than I am. I do this for fun, not points!
0
 

Author Comment

by:lucyLuna
ID: 37820232
Thank you both Capricorn1, and Flyster...the report look great, problem solved...

SOOOOOOOOOOO thankful to both of you.


Lucy
0
 

Assisted Solution

by:lucyLuna
lucyLuna earned 0 total points
ID: 37820384
I've requested that this question be closed as follows:

Accepted answer: 500 points for Flyster's comment #37819719
Assisted answer: 0 points for lucyLuna's comment #37820232

for the following reason:

Solution was easy to implement for a beginner, I realize that not getting the correct solution right away is because my question was not properly constructed. <br /><br />I am learning...thanks much!<br /><br />Lucy
0
 
LVL 22

Expert Comment

by:Flyster
ID: 37820385
I believe the points should be split between myself and Capricorn1. He answered the second part of the question.

To Lucy: Thanks!
0
 

Author Comment

by:lucyLuna
ID: 37820466
Flyster, I agree..I just don't know what or how to make sure the points are split between you two. Please let me know what I should do to make this happen.


Thanks,

Lucy
0
 

Author Closing Comment

by:lucyLuna
ID: 37854997
Sorry this took so long to resolve, you both were very helpful and together gave me the solution...which worked GREAT!

Thank you both very much,

Lucy
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now