Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Y/N field Report in Access 2010

Posted on 2012-04-07
15
Medium Priority
?
595 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37819783
lucyluna,

here is some123 QBE view of the query
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 

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 1000 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

715 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