Yes/No Query Question
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
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
but then that becomes a parameter query
When do u decide what u want to display?
Why cant u buld your logic round that
When do u decide what u want to display?
Why cant u buld your logic round that
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.
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.
ASKER
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
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
WHERE myyesnofield LIKE IIF(NZ([Remote Site],"*")="Y",-1,0)
ASKER
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
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
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
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
ok, just to accept Y/N
anything else is all
WHERE myyesnofield LIKE IIF( NZ([Remote Site],"*")="Y",-1, NZ([Remote Site],"*")="N",0, "*")
anything else is all
WHERE myyesnofield LIKE IIF( NZ([Remote Site],"*")="Y",-1, NZ([Remote Site],"*")="N",0, "*")
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]
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]
I think the trick here is to make everthing 'disappear' after the WHERE when nothing is entered.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awsome!
That's Exactly what I was looking for!!!!!!!!!!!!!!!!!
You Rock! (rockiroads)
Mark
That's Exactly what I was looking for!!!!!!!!!!!!!!!!!
You Rock! (rockiroads)
Mark
smithmrk: I tested my answer on a test table and it worked. Did you try it?
No probs
WHERE myyesnofield = IIF([Enter Y or N]="Y",-1,0)