Solved

Y/N field Report in Access 2010

Posted on 2012-04-07
15
593 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Suggested Courses

635 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