How to *not* put a field on a report

  • Thread starter Thread starter James Ivey
  • Start date Start date
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
 
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
Click to expand...

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.
 
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
 
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
Click to expand...

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...
 
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
Click to expand...

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.
Click to expand...
 
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...
Click to expand...

Yes, I want to include the person, but hide their phone number/email
 
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"));
 
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
Click to expand...
Click to expand...
 
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........... You Rock!!!!

Oh man, it works perfectly.

I'm so happy :o)))))

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"));
Click to expand...
Click to expand...
 
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
 
Back
Top