How to *not* put a field on a report

J

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
 
P

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.
 
J

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
 
P

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...
 
J

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.
 
J

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
 
J

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"));
 
J

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"));




James Ivey said:
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 said:
Yes, I want to include the person, but hide their phone number/email
 
S

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"));
 
J

James Ivey

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

Oh man, it works perfectly.

I'm so happy :blush:)))))

Thank you, Thank you, Thank you.

James


Steve Schapel said:
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

James said:
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"));
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top