Query on "Yes" Brings Back both

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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...
 
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...
 
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...
 
Back
Top