How to *not* put a field on a report

[James Ivey]

Access newbie here. Am not a programmer, so I need help with this one.

I've created a simple little database with membership info for our
organization of approx 450 members. Name, Address, email, phone numbers...
a few other bits of info.

I've got a field on the form called "Don't include email in Directory" with
a Yes or No combo box.

Same deal with the phone number.

Each year we print a Membership Directory with Name, Address, Phone and
Email.

I've set this up in Access with a Report using the mailing labels report.

Several members don't want their Email address or Phone number in the
Directory.

What I want to do, is say:

If "Don't include email in Directory" = Yes, then don't put it on the Report

Can I make that happen programatically?

Thanks in advance for any help on this one.

James

 

[pietlinden]

Access newbie here. Am not a programmer, so I need help with this one.

I've created a simple little database with membership info for our
organization of approx 450 members. Name, Address, email, phone numbers...
a few other bits of info.

I've got a field on the form called "Don't include email in Directory" with
a Yes or No combo box.

Same deal with the phone number.

Each year we print a Membership Directory with Name, Address, Phone and
Email.

I've set this up in Access with a Report using the mailing labels report.

Several members don't want their Email address or Phone number in the
Directory.

What I want to do, is say:

If "Don't include email in Directory" = Yes, then don't put it on the Report

Can I make that happen programatically?

Thanks in advance for any help on this one.

James

base the report on a query, then in the query, create a field for
EMailAddress and Phone Number. If "Don't Include" for the field in
question is true, just set the value of the calculated field to null.

ShowPhone = IIF({DontInclude]=True,"",[PhoneNo])

Do the same for e-mail. then base the report on your query and you're
set.

 

[James Ivey]

Sheesh... I didn't word this very well.

The fields are called:
Phone_on_list Yes/No (combo box)
Email_on_list Yes/No (combo box)

So, the statement would be:
If Phone_on_list = No, then don't print it on the Report.

Hope that helps.

James

 

[pietlinden]

Sheesh... I didn't word this very well.

The fields are called:
Phone_on_list Yes/No (combo box)
Email_on_list Yes/No (combo box)

So, the statement would be:
If Phone_on_list = No, then don't print it on the Report.

Hope that helps.

James

So show/hide the phone number/e-mail or exclude the person? If you
want to exclude the person, just filter out the "No" records. If you
do want to show the name, but no e-mail or no phone, you could either
create a query and base the report on it, or you could create unbound
controls on your report that did the calculations for you... the
calculations would be the same IIF stuff...

 

[James Ivey]

Thanks Piet... I really appreciate your help.

Here's what the query looks like.

SELECT [Members Table].First_Name, [Members Table].Last_Name, [Members
Table].Address, [Members Table].City, [Members Table].[State/Province],
[Members Table].Postal_Code, [Members Table].[Country/Region], [Members
Table].Home_Phone, [Members Table].Email_Address, [Members Table].Website,
[Members Table].Phone_on_List
FROM [Members Table]
WHERE ((([Members Table].Membership_Status)="Active" Or ([Members
Table].Membership_Status)="New member") AND
("ShowPhone"=IIf([Phone_on_List]=No,"",[Home_Phone])));

But the resulting list when I run the query gives me no records..?

Did I set it up correctly?

James

Access newbie here. Am not a programmer, so I need help with this one.

I've created a simple little database with membership info for our
organization of approx 450 members. Name, Address, email, phone
numbers...
a few other bits of info.

I've got a field on the form called "Don't include email in Directory"
with
a Yes or No combo box.

Same deal with the phone number.

Each year we print a Membership Directory with Name, Address, Phone and
Email.

I've set this up in Access with a Report using the mailing labels report.

Several members don't want their Email address or Phone number in the
Directory.

What I want to do, is say:

If "Don't include email in Directory" = Yes, then don't put it on the
Report

Can I make that happen programatically?

Thanks in advance for any help on this one.

James

base the report on a query, then in the query, create a field for
EMailAddress and Phone Number. If "Don't Include" for the field in
question is true, just set the value of the calculated field to null.

ShowPhone = IIF({DontInclude]=True,"",[PhoneNo])

Do the same for e-mail. then base the report on your query and you're
set.

 

[James Ivey]

So show/hide the phone number/e-mail or exclude the person? If you
want to exclude the person, just filter out the "No" records. If you
do want to show the name, but no e-mail or no phone, you could either
create a query and base the report on it, or you could create unbound
controls on your report that did the calculations for you... the
calculations would be the same IIF stuff...

Yes, I want to include the person, but hide their phone number/email

 

[James Ivey]

Here's the query without any of my fumbling efforts added:

SELECT [Members Table].First_Name, [Members Table].Last_Name, [Members
Table].Address, [Members Table].City, [Members Table].[State/Province],
[Members Table].Postal_Code, [Members Table].[Country/Region], [Members
Table].Home_Phone, [Members Table].Email_Address, [Members Table].Website
FROM [Members Table]
WHERE ((([Members Table].Membership_Status)="Active" Or ([Members
Table].Membership_Status)="New member"));

 

[James Ivey]

Here's another feeble effort that results in no records listed:

SELECT [Members Table].First_Name, [Members Table].Last_Name, [Members
Table].Address, [Members Table].City, [Members Table].[State/Province],
[Members Table].Postal_Code, [Members Table].[Country/Region], [Members
Table].Home_Phone, [Members Table].Email_Address, [Members Table].Website,
[Members Table].Phone_on_List
FROM [Members Table]
WHERE ((([Members
Table].Home_Phone)=IIf("Phone_in_List"=No,"","Home_Phone")) AND (([Members
Table].Membership_Status)="Active" Or ([Members
Table].Membership_Status)="New member"));

Here's the query without any of my fumbling efforts added:

SELECT [Members Table].First_Name, [Members Table].Last_Name, [Members
Table].Address, [Members Table].City, [Members Table].[State/Province],
[Members Table].Postal_Code, [Members Table].[Country/Region], [Members
Table].Home_Phone, [Members Table].Email_Address, [Members Table].Website
FROM [Members Table]
WHERE ((([Members Table].Membership_Status)="Active" Or ([Members
Table].Membership_Status)="New member"));

Yes, I want to include the person, but hide their phone number/email

 

[Steve Schapel]

James,

Assuming I understand you correctly, try...

SELECT [Members Table].First_Name, [Members Table].Last_Name, [Members
Table].Address, [Members Table].City, [Members Table].[State/Province],
[Members Table].Postal_Code, [Members Table].[Country/Region],
IIf([Members Table].[Phone_on_list]="yes",[Members
Table].Home_Phone,Null) As Phone, IIf([Members
Table].[Email_on_list]="yes",[Members Table].Email_Address,Null) As
Email, [Members Table].Website
FROM [Members Table]
WHERE ((([Members Table].Membership_Status)="Active" Or ([Members
Table].Membership_Status)="New member"));

 

[James Ivey]

Steve........... You Rock!!!!

Oh man, it works perfectly.

I'm so happy )))))

Thank you, Thank you, Thank you.

James

James,

Assuming I understand you correctly, try...

SELECT [Members Table].First_Name, [Members Table].Last_Name, [Members
Table].Address, [Members Table].City, [Members Table].[State/Province],
[Members Table].Postal_Code, [Members Table].[Country/Region],
IIf([Members Table].[Phone_on_list]="yes",[Members Table].Home_Phone,Null)
As Phone, IIf([Members Table].[Email_on_list]="yes",[Members
Table].Email_Address,Null) As Email, [Members Table].Website
FROM [Members Table]
WHERE ((([Members Table].Membership_Status)="Active" Or ([Members
Table].Membership_Status)="New member"));

--
Steve Schapel, Microsoft Access MVP

Here's the query without any of my fumbling efforts added:

SELECT [Members Table].First_Name, [Members Table].Last_Name, [Members
Table].Address, [Members Table].City, [Members Table].[State/Province],
[Members Table].Postal_Code, [Members Table].[Country/Region], [Members
Table].Home_Phone, [Members Table].Email_Address, [Members Table].Website
FROM [Members Table]
WHERE ((([Members Table].Membership_Status)="Active" Or ([Members
Table].Membership_Status)="New member"));

 

[James Ivey]

Piet,

I just wanted to Thank You for your efforts.

I'm betting your way works too... I think I'm just too green a newbie to
implement it properly.

But I sure appreciate you trying to help me out.

James