Solved

Y/N field Report in Access 2010

Posted on 2012-04-07
15
591 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 120

Expert Comment

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

here is some123 QBE view of the query
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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…

685 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