Query on "Yes" Brings Back both

N

Novice2000

Hi,

I have created a table where the value is Yes/No - text with default as No.
I have a query in which I am trying to bring back only the "Yes", but both
yes and no are being shown.

Any ideas? Thanks
 
O

Ofer Cohen

Can you post the SQL you tried?

Also, try this SQL after changing the name of table and fields

Select * From TableName Where Nz([YesNoFieldName],False)=True
 
N

Novice2000

It keeps saying "Data Type mismatch" but how can that be when I created the
query from scratch?


SELECT [all 05].Examiner, [all 05].[Risk Number], [all 05].Company, [all
05].Address1, [all 05].Address2, [all 05].City, [all 05].State, [all
05].[Postal Code], [all 05].Assn, [all 05].[07 Group], [all 05].[Retainer for
2007], [all 05].[Claim Salutation], [all 05].[Claim First Name], [all
05].[Claim Last Name], [all 05].[07 Fee], [all 05].[07 Amount Pd - 1], [all
05].[07 Amount Pd - 2], [all 05].[07 Special Billing]
FROM [all 05]
WHERE ((([all 05].Assn)<>"cancel" And ([all 05].Assn)<>"jre" And ([all
05].Assn)<>"spo1" And ([all 05].Assn)<>"agwc" And ([all 05].Assn)<>"combo"
And ([all 05].Assn)<>"gms" And ([all 05].Assn)<>"acr" And ([all
05].Assn)<>"b&a" And ([all 05].Assn)<>"brf" And ([all 05].Assn)<>"cpm" And
([all 05].Assn)<>"dm" And ([all 05].Assn)<>"dob" And ([all 05].Assn)<>"erm"
And ([all 05].Assn)<>"kksg" And ([all 05].Assn)<>"obb" And ([all
05].Assn)<>"oca" And ([all 05].Assn)<>"orm" And ([all 05].Assn)<>"phr" And
([all 05].Assn)<>"rbs" And ([all 05].Assn)<>"scs" And ([all 05].Assn)<>"spo"
And ([all 05].Assn)<>"w&c") AND (([all 05].[07 Group])="yes") AND (([all
05].[Claim Last Name]) Is Not Null)) OR ((([all 05].Assn)<>"cancel" And ([all
05].Assn)<>"jre" And ([all 05].Assn)<>"spo1" And ([all 05].Assn)<>"agwc" And
([all 05].Assn)<>"combo" And ([all 05].Assn)<>"gms" And ([all
05].Assn)<>"acr" And ([all 05].Assn)<>"b&a" And ([all 05].Assn)<>"brf" And
([all 05].Assn)<>"cpm" And ([all 05].Assn)<>"dm" And ([all 05].Assn)<>"dob"
And ([all 05].Assn)<>"erm" And ([all 05].Assn)<>"kksg" And ([all
05].Assn)<>"obb" And ([all 05].Assn)<>"oca" And ([all 05].Assn)<>"orm" And
([all 05].Assn)<>"phr" And ([all 05].Assn)<>"rbs" And ([all 05].Assn)<>"scs"
And ([all 05].Assn)<>"spo" And ([all 05].Assn)<>"w&c") AND (([all
05].[Retainer for 2007])=Yes) AND (([all 05].[Claim Last Name]) Is Not Null));


Ofer Cohen said:
Can you post the SQL you tried?

Also, try this SQL after changing the name of table and fields

Select * From TableName Where Nz([YesNoFieldName],False)=True

--
Good Luck
BS"D


Novice2000 said:
Hi,

I have created a table where the value is Yes/No - text with default as No.
I have a query in which I am trying to bring back only the "Yes", but both
yes and no are being shown.

Any ideas? Thanks
Click to expand...
Click to expand...
 
J

John Spencer

If the field is a TEXT field and contains "Yes" or "No" then you need to use
quote marks around the value.

[all 05].[Retainer for 2007]="Yes"

Yes (no quotes) is recognized as a constant by Access SQL with the value
of -1 (True)
No (no quotes) is recognized as a constant by Access SQL with the value of 0
(False)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Novice2000 said:
It keeps saying "Data Type mismatch" but how can that be when I created
the
query from scratch?


SELECT [all 05].Examiner, [all 05].[Risk Number], [all 05].Company, [all
05].Address1, [all 05].Address2, [all 05].City, [all 05].State, [all
05].[Postal Code], [all 05].Assn, [all 05].[07 Group], [all 05].[Retainer
for
2007], [all 05].[Claim Salutation], [all 05].[Claim First Name], [all
05].[Claim Last Name], [all 05].[07 Fee], [all 05].[07 Amount Pd - 1],
[all
05].[07 Amount Pd - 2], [all 05].[07 Special Billing]
FROM [all 05]
WHERE ((([all 05].Assn)<>"cancel" And ([all 05].Assn)<>"jre" And ([all
05].Assn)<>"spo1" And ([all 05].Assn)<>"agwc" And ([all 05].Assn)<>"combo"
And ([all 05].Assn)<>"gms" And ([all 05].Assn)<>"acr" And ([all
05].Assn)<>"b&a" And ([all 05].Assn)<>"brf" And ([all 05].Assn)<>"cpm" And
([all 05].Assn)<>"dm" And ([all 05].Assn)<>"dob" And ([all
05].Assn)<>"erm"
And ([all 05].Assn)<>"kksg" And ([all 05].Assn)<>"obb" And ([all
05].Assn)<>"oca" And ([all 05].Assn)<>"orm" And ([all 05].Assn)<>"phr" And
([all 05].Assn)<>"rbs" And ([all 05].Assn)<>"scs" And ([all
05].Assn)<>"spo"
And ([all 05].Assn)<>"w&c") AND (([all 05].[07 Group])="yes") AND (([all
05].[Claim Last Name]) Is Not Null)) OR ((([all 05].Assn)<>"cancel" And
([all
05].Assn)<>"jre" And ([all 05].Assn)<>"spo1" And ([all 05].Assn)<>"agwc"
And
([all 05].Assn)<>"combo" And ([all 05].Assn)<>"gms" And ([all
05].Assn)<>"acr" And ([all 05].Assn)<>"b&a" And ([all 05].Assn)<>"brf" And
([all 05].Assn)<>"cpm" And ([all 05].Assn)<>"dm" And ([all
05].Assn)<>"dob"
And ([all 05].Assn)<>"erm" And ([all 05].Assn)<>"kksg" And ([all
05].Assn)<>"obb" And ([all 05].Assn)<>"oca" And ([all 05].Assn)<>"orm" And
([all 05].Assn)<>"phr" And ([all 05].Assn)<>"rbs" And ([all
05].Assn)<>"scs"
And ([all 05].Assn)<>"spo" And ([all 05].Assn)<>"w&c") AND (([all
05].[Retainer for 2007])=Yes) AND (([all 05].[Claim Last Name]) Is Not
Null));


Ofer Cohen said:
Can you post the SQL you tried?

Also, try this SQL after changing the name of table and fields

Select * From TableName Where Nz([YesNoFieldName],False)=True

--
Good Luck
BS"D


Novice2000 said:
Hi,

I have created a table where the value is Yes/No - text with default as
No.
I have a query in which I am trying to bring back only the "Yes", but
both
yes and no are being shown.

Any ideas? Thanks
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer Cohen

In addition to John, consider using

Not In("cancel" ,"jre","spo1", "agwc","combo","gms","acr", "b&a","brf"
,"cpm" ,"dm","dob" ,"erm","kksg" ,"obb","oca" ,"orm" ,"phr" ,"rbs" ,"scs"
,"spo","w&c")

As a criteria instead of <>
--
Good Luck
BS"D


John Spencer said:
If the field is a TEXT field and contains "Yes" or "No" then you need to use
quote marks around the value.

[all 05].[Retainer for 2007]="Yes"

Yes (no quotes) is recognized as a constant by Access SQL with the value
of -1 (True)
No (no quotes) is recognized as a constant by Access SQL with the value of 0
(False)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Novice2000 said:
It keeps saying "Data Type mismatch" but how can that be when I created
the
query from scratch?


SELECT [all 05].Examiner, [all 05].[Risk Number], [all 05].Company, [all
05].Address1, [all 05].Address2, [all 05].City, [all 05].State, [all
05].[Postal Code], [all 05].Assn, [all 05].[07 Group], [all 05].[Retainer
for
2007], [all 05].[Claim Salutation], [all 05].[Claim First Name], [all
05].[Claim Last Name], [all 05].[07 Fee], [all 05].[07 Amount Pd - 1],
[all
05].[07 Amount Pd - 2], [all 05].[07 Special Billing]
FROM [all 05]
WHERE ((([all 05].Assn)<>"cancel" And ([all 05].Assn)<>"jre" And ([all
05].Assn)<>"spo1" And ([all 05].Assn)<>"agwc" And ([all 05].Assn)<>"combo"
And ([all 05].Assn)<>"gms" And ([all 05].Assn)<>"acr" And ([all
05].Assn)<>"b&a" And ([all 05].Assn)<>"brf" And ([all 05].Assn)<>"cpm" And
([all 05].Assn)<>"dm" And ([all 05].Assn)<>"dob" And ([all
05].Assn)<>"erm"
And ([all 05].Assn)<>"kksg" And ([all 05].Assn)<>"obb" And ([all
05].Assn)<>"oca" And ([all 05].Assn)<>"orm" And ([all 05].Assn)<>"phr" And
([all 05].Assn)<>"rbs" And ([all 05].Assn)<>"scs" And ([all
05].Assn)<>"spo"
And ([all 05].Assn)<>"w&c") AND (([all 05].[07 Group])="yes") AND (([all
05].[Claim Last Name]) Is Not Null)) OR ((([all 05].Assn)<>"cancel" And
([all
05].Assn)<>"jre" And ([all 05].Assn)<>"spo1" And ([all 05].Assn)<>"agwc"
And
([all 05].Assn)<>"combo" And ([all 05].Assn)<>"gms" And ([all
05].Assn)<>"acr" And ([all 05].Assn)<>"b&a" And ([all 05].Assn)<>"brf" And
([all 05].Assn)<>"cpm" And ([all 05].Assn)<>"dm" And ([all
05].Assn)<>"dob"
And ([all 05].Assn)<>"erm" And ([all 05].Assn)<>"kksg" And ([all
05].Assn)<>"obb" And ([all 05].Assn)<>"oca" And ([all 05].Assn)<>"orm" And
([all 05].Assn)<>"phr" And ([all 05].Assn)<>"rbs" And ([all
05].Assn)<>"scs"
And ([all 05].Assn)<>"spo" And ([all 05].Assn)<>"w&c") AND (([all
05].[Retainer for 2007])=Yes) AND (([all 05].[Claim Last Name]) Is Not
Null));


Ofer Cohen said:
Can you post the SQL you tried?

Also, try this SQL after changing the name of table and fields

Select * From TableName Where Nz([YesNoFieldName],False)=True

--
Good Luck
BS"D


:

Hi,

I have created a table where the value is Yes/No - text with default as
No.
I have a query in which I am trying to bring back only the "Yes", but
both
yes and no are being shown.

Any ideas? Thanks
Click to expand...
Click to expand...
Click to expand...
 
Top