Criteria - show records in text field1 based on a yes/no field2

[jriccardi]

In our homeowners association, we have a directory and some of the folks do
not want some of their personal data to appear in our directory. So among
many other fields in my database, I have fields in my table/query and I want
to show records in text field1 based on the results of a yes/no field2, For
example, I only want to show the text field1 record data (phone #) if the
yes/no field2 is checked (yes). How do I implement in query criteria?
Please help...

 

[KARL DEWEY]

Easiest way is with an IIF statement --
Phone: IIF([field2] = True, [field1], "")
OR
Phone: IIF([field2] = True, [field1], "Unlisted")

 

[John Spencer]

In a query, when you want to show the value sometimes and hide the value
on other records, you would use a calculated field. That looks
something like the following if you are using the design view (query grid).

Field: ShowPhone: IIF([Field2] = True,[Phone #],Null)



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

 

[jriccardi]

I need the opposite logic - IIF([field2] = False, [field1], ""), however...

I tried your suggestion but it does not return all records now, it only
returns the records with the [field2] = False. Also, [field1] is not blank
("").

I want all records returned; I just want the [field1] data blank if [field2]
= False.

Please let me know what I am still doing wrong...

Thanks.

Easiest way is with an IIF statement --
Phone: IIF([field2] = True, [field1], "")
OR
Phone: IIF([field2] = True, [field1], "Unlisted")
--
KARL DEWEY
Build a little - Test a little

In our homeowners association, we have a directory and some of the folks do
not want some of their personal data to appear in our directory. So among
many other fields in my database, I have fields in my table/query and I want
to show records in text field1 based on the results of a yes/no field2, For
example, I only want to show the text field1 record data (phone #) if the
yes/no field2 is checked (yes). How do I implement in query criteria?
Please help...

 

[jriccardi]

I need the opposite logic - IIF([field2] = False, [field1], ""), however...

I am getting the same results as the last suggestion. It does not return
all records now, it only returns the records with the [field2] = False.
Also, [field1] is still not blank (Null).

I want all records returned; I just want the [field1] data blank if [field2]
= False.

Please let me know what I am still doing wrong...

Thanks.

In a query, when you want to show the value sometimes and hide the value
on other records, you would use a calculated field. That looks
something like the following if you are using the design view (query grid).

Field: ShowPhone: IIF([Field2] = True,[Phone #],Null)



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

In our homeowners association, we have a directory and some of the folks do
not want some of their personal data to appear in our directory. So among
many other fields in my database, I have fields in my table/query and I want
to show records in text field1 based on the results of a yes/no field2, For
example, I only want to show the text field1 record data (phone #) if the
yes/no field2 is checked (yes). How do I implement in query criteria?
Please help...

 

[jriccardi]

Just to make sure, this is the criteria I entered...

IIf([Directory]=False,[Phone #],Null)
OR
IIf([Directory]=False,[Phone #],"")

Neither one is working as explained in my previous replies...

I need the opposite logic - IIF([field2] = False, [field1], ""), however...

I am getting the same results as the last suggestion. It does not return
all records now, it only returns the records with the [field2] = False.
Also, [field1] is still not blank (Null).

I want all records returned; I just want the [field1] data blank if [field2]
= False.

Please let me know what I am still doing wrong...

Thanks.

In a query, when you want to show the value sometimes and hide the value
on other records, you would use a calculated field. That looks
something like the following if you are using the design view (query grid).

Field: ShowPhone: IIF([Field2] = True,[Phone #],Null)



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

In our homeowners association, we have a directory and some of the folks do
not want some of their personal data to appear in our directory. So among
many other fields in my database, I have fields in my table/query and I want
to show records in text field1 based on the results of a yes/no field2, For
example, I only want to show the text field1 record data (phone #) if the
yes/no field2 is checked (yes). How do I implement in query criteria?
Please help...

 

[John Spencer]

What I proposed was not criteria. It was a calculated expression to be used
in as a FIELD in the query.

In design view (the query grid), you would see something like the following

Field: ShowPhone: IIf([Directory]=False,[Phone #],Null)
Table: <BLANK>
Criteria: <BLANK>

Although I would think you logic is reversed. If Directory were true I would
show the phone number, but perhaps in your database - if Directory is checked
you mean that the information should NOT appear in the Directory.

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

Just to make sure, this is the criteria I entered...

IIf([Directory]=False,[Phone #],Null)
OR
IIf([Directory]=False,[Phone #],"")

Neither one is working as explained in my previous replies...

I need the opposite logic - IIF([field2] = False, [field1], ""), however...

I am getting the same results as the last suggestion. It does not return
all records now, it only returns the records with the [field2] = False.
Also, [field1] is still not blank (Null).

I want all records returned; I just want the [field1] data blank if [field2]
= False.

Please let me know what I am still doing wrong...

Thanks.

In a query, when you want to show the value sometimes and hide the value
on other records, you would use a calculated field. That looks
something like the following if you are using the design view (query grid).

Field: ShowPhone: IIF([Field2] = True,[Phone #],Null)



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


jriccardi wrote:
In our homeowners association, we have a directory and some of the folks do
not want some of their personal data to appear in our directory. So among
many other fields in my database, I have fields in my table/query and I want
to show records in text field1 based on the results of a yes/no field2, For
example, I only want to show the text field1 record data (phone #) if the
yes/no field2 is checked (yes). How do I implement in query criteria?
Please help...

 

[jriccardi]

John,

Thank you very much for the answer and your patient explanation. I
misunderstood your 1st response as I have never used a calculated expression.
It works.

Thanks again.

Joe

PS. And my thanks to Karl as I suspect I also misunderstood your suggestion.

What I proposed was not criteria. It was a calculated expression to be used
in as a FIELD in the query.

In design view (the query grid), you would see something like the following

Field: ShowPhone: IIf([Directory]=False,[Phone #],Null)
Table: <BLANK>
Criteria: <BLANK>

Although I would think you logic is reversed. If Directory were true I would
show the phone number, but perhaps in your database - if Directory is checked
you mean that the information should NOT appear in the Directory.

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

Just to make sure, this is the criteria I entered...

IIf([Directory]=False,[Phone #],Null)
OR
IIf([Directory]=False,[Phone #],"")

Neither one is working as explained in my previous replies...

I need the opposite logic - IIF([field2] = False, [field1], ""), however...

I am getting the same results as the last suggestion. It does not return
all records now, it only returns the records with the [field2] = False.
Also, [field1] is still not blank (Null).

I want all records returned; I just want the [field1] data blank if [field2]
= False.

Please let me know what I am still doing wrong...

Thanks.


:

In a query, when you want to show the value sometimes and hide the value
on other records, you would use a calculated field. That looks
something like the following if you are using the design view (query grid).

Field: ShowPhone: IIF([Field2] = True,[Phone #],Null)



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


jriccardi wrote:
In our homeowners association, we have a directory and some of the folks do
not want some of their personal data to appear in our directory. So among
many other fields in my database, I have fields in my table/query and I want
to show records in text field1 based on the results of a yes/no field2, For
example, I only want to show the text field1 record data (phone #) if the
yes/no field2 is checked (yes). How do I implement in query criteria?
Please help...