Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Yes/No Query Question

Posted on 2006-06-07
14
Medium Priority
?
190 Views
Last Modified: 2006-11-18
Hello Experts!

I have a Yes/No Question with my query.  Sometimes I want the Yes values and sometimes I want ALL the values.  The wildcard * doesn't work with Boolean (Yes/No).  

Is there someway when I pass in the paramater for Yes/No I can pass something like * that would give me all the values not just Yes or No.

Or can I ignore the paramater somehow that I get all the values?

Thanks,
smithmrk
0
Comment
Question by:smithmrk
  • 7
  • 4
  • 3
14 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16855255
U could try this


WHERE myyesnofield = IIF([Enter Y or N]="Y",-1,0)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16855264
but then that becomes a parameter query

When do u decide what u want to display?

Why cant u buld your logic round that
0
 
LVL 44

Expert Comment

by:GRayL
ID: 16855469
SELECT a.*
FROM myTable AS a
WHERE iif(IsNull([enter True or leave blank for all]),"",a.YNfld = true);

Assumes a table named myTable and the yesno field is named YNfld.  Put in your actual names.
0
Independent Software Vendors: 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!

 

Author Comment

by:smithmrk
ID: 16856014
Let me try to be a little more clear on what I'm looking for.

The Field is a Yes/No (Boolean) Field with Check Marks for Yes.

The Query asks the user for a Parameter "Remote Site"
If the user puts Yes then just pull the Yes Records.
If the user puts No then just pull the No Records.
If the user puts nothing pull ALL the Records (Yes and No).

The Paramater [RemoteSite] is a Yes/No Type.

How can I accomplish this?

Thanks,
smithmrk
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16856028
WHERE myyesnofield LIKE IIF(NZ([Remote Site],"*")="Y",-1,0)



0
 

Author Comment

by:smithmrk
ID: 16856240
Rockiroads,

When I use your statement:

WHERE myyesnofield LIKE IIF(NZ([Remote Site],"*")="Y",-1,0)

When I put in "Y" I get the Yes Records
When I put in "N" I get the No Records
When I put in "*" I get the No Records - I want ALL the Records

Thanks,
smithmrk
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16856312
urm I thought u said when user enters nothing

what happens if u dont enter anything?

what Im checking for is


if user enters nothing then display all
if user enters Y then display Y records,
otherwise any other input, it displays N


0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16856326
ok, just to accept Y/N
anything else is all


WHERE myyesnofield LIKE   IIF(    NZ([Remote Site],"*")="Y",-1,   NZ([Remote Site],"*")="N",0, "*")

0
 
LVL 44

Expert Comment

by:GRayL
ID: 16856372
PARAMETERS [Enter Yes or No or leave blank for all] Bit;
SELECT a.* FROM myTable AS a
WHERE Iif(IsNull([Enter Yes or No or leave blank for all]),"",Iif([Enter Yes or No or leave blank for all] = Yes, a.[Remote Site] = -1,a.[Remote Site] = 0));


This assumes a table named myTable and a Yes/No field named [Remote Site]
0
 
LVL 44

Expert Comment

by:GRayL
ID: 16856388
I think the trick here is to make everthing 'disappear' after the WHERE when nothing is entered.
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 16856426
I forgot the IIF doh!


WHERE myyesnofield LIKE   IIF(    NZ([Remote Site],"*")="Y",-1,   IIF(NZ([Remote Site],"*")="N",0, "*"))



0
 

Author Comment

by:smithmrk
ID: 16856496
Awsome!

That's Exactly what I was looking for!!!!!!!!!!!!!!!!!

You Rock! (rockiroads)

Mark
0
 
LVL 44

Expert Comment

by:GRayL
ID: 16856548
smithmrk:  I tested my answer on a test table and it worked.  Did you try it?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16856702
No probs
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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

572 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