Not print if field is empty

[Susan]

Hello! This situation is similar to one answered here earlier, but with a
twist, so here goes. By the way, I am using Access 97. I have two fields,
HomeFax and BusFax. I want them to be visible on a report only if they have
data. In addition, I want to include the word "FAX - " in the expression,
as in ("FAX - "&[HomeFax]) to differentiate between the voice phone number
and the fax number. I got that to work fine, but if there is no data in the
field, I get the dreaded "#Error" message. Actually, what I would prefer is
to have the fields below the FAX fields move up so there is no space if the
FAX field is empty. I can normally accomplish this by making the fields
very small and setting the "can grow" and "can shrink" properties. This
works except for all except the expression field because of the additional
word FAX. Is there any way I can accomplish this? I don't know any coding,
so please be gentle, if possible! If this helps, what I am doing is this:

Name1 BusAdd HomeAdd
BusPhone HomePhone
BusFax HomeFax

Name2 etc. etc.

Thanks in advance.

Susan

 

[Rick B]

To include the literal only if there is data in the field, use the "+" not
the "&" in an unbound text box....

="Fax - " + [HomeFax]

The "+" will print the two items only if they both contain data.

 

[OfficeDev18 via AccessMonster.com]

Hi, Susan,

Make sure the name of the Fax control is not exactly the same name as the
field name in the form's record source. Having done that, you are free to
modify the textbox's control source property as follows:

=IIf(Nz(HomeFax," ")=" ","","FAX - " & HomeFax)

Repeat both steps for the business fax. That should do it.

Sam

Hello! This situation is similar to one answered here earlier, but with a
twist, so here goes. By the way, I am using Access 97. I have two fields,
HomeFax and BusFax. I want them to be visible on a report only if they have
data. In addition, I want to include the word "FAX - " in the expression,
as in ("FAX - "&[HomeFax]) to differentiate between the voice phone number
and the fax number. I got that to work fine, but if there is no data in the
field, I get the dreaded "#Error" message. Actually, what I would prefer is
to have the fields below the FAX fields move up so there is no space if the
FAX field is empty. I can normally accomplish this by making the fields
very small and setting the "can grow" and "can shrink" properties. This
works except for all except the expression field because of the additional
word FAX. Is there any way I can accomplish this? I don't know any coding,
so please be gentle, if possible! If this helps, what I am doing is this:

Name1 BusAdd HomeAdd
BusPhone HomePhone
BusFax HomeFax

Name2 etc. etc.

Thanks in advance.

Susan

 

[Susan]

Thanks...I'm about to leave for the day, but I'll try it tomorrow and let
you know how it went. I appreciate the quick response, though!

Susan

Hi, Susan,

Make sure the name of the Fax control is not exactly the same name as the
field name in the form's record source. Having done that, you are free to
modify the textbox's control source property as follows:

=IIf(Nz(HomeFax," ")=" ","","FAX - " & HomeFax)

Repeat both steps for the business fax. That should do it.

Sam

Hello! This situation is similar to one answered here earlier, but with a
twist, so here goes. By the way, I am using Access 97. I have two fields,
HomeFax and BusFax. I want them to be visible on a report only if they have
data. In addition, I want to include the word "FAX - " in the expression,
as in ("FAX - "&[HomeFax]) to differentiate between the voice phone number
and the fax number. I got that to work fine, but if there is no data in the
field, I get the dreaded "#Error" message. Actually, what I would prefer is
to have the fields below the FAX fields move up so there is no space if the
FAX field is empty. I can normally accomplish this by making the fields
very small and setting the "can grow" and "can shrink" properties. This
works except for all except the expression field because of the additional
word FAX. Is there any way I can accomplish this? I don't know any coding,
so please be gentle, if possible! If this helps, what I am doing is this:

Name1 BusAdd HomeAdd
BusPhone HomePhone
BusFax HomeFax

Name2 etc. etc.

Thanks in advance.

Susan

 

[Guest]

thanks to both responders. I tried the "+" first because it was the more
direct approach, and it worked. But now I know two things I didn't know
before. I appreciate all you experts who take the time to help us newbies!

 

[OfficeDev18 via AccessMonster.com]

Hey,

I never knew Rick's neat trick before either, and I learned something too.

Thanks, Rick.

Sam

thanks to both responders. I tried the "+" first because it was the more
direct approach, and it worked. But now I know two things I didn't know
before. I appreciate all you experts who take the time to help us newbies!

Hello! This situation is similar to one answered here earlier, but with a
twist, so here goes. By the way, I am using Access 97. I have two fields,

[quoted text clipped - 19 lines]

Susan