Way to eliminate rows with field header but empty field?

[StargateFan]

I've used CanShrink in all fields and in the section. Everything
shrinks beautifully. However, one line that deals with a field for
fax numbers has a text header that looks like this:

=Trim("Fax: " & [FaxNumber])

Of course, the "Fax: " part is actual text so that appears in this
report whether or not there is a fax number. I'm hoping there's
another way to do this, then, that would make the field empty and
shrink when there isn't a fax number in the record.

Is there a way to do this, pls?

Thank you! D

 

[Duane Hookom]

Try:
="Fax: " + [FaxNumber]

 

[Joe Cilinceon]

I've used CanShrink in all fields and in the section. Everything
shrinks beautifully. However, one line that deals with a field for
fax numbers has a text header that looks like this:

=Trim("Fax: " & [FaxNumber])

Of course, the "Fax: " part is actual text so that appears in this
report whether or not there is a fax number. I'm hoping there's
another way to do this, then, that would make the field empty and
shrink when there isn't a fax number in the record.

Is there a way to do this, pls?

Thank you! D

This might work;
Iif(IsNull([FaxNumber])=False,"Fax: " & Trim([FaxNumber]),"")

 

[Duane Hookom]

A small modification to this:
=IIf(IsNull([FaxNumber])=False,"Fax: " & Trim([FaxNumber]),Null)


--
Duane Hookom
MS Access MVP
--

I've used CanShrink in all fields and in the section. Everything
shrinks beautifully. However, one line that deals with a field for
fax numbers has a text header that looks like this:

=Trim("Fax: " & [FaxNumber])

Of course, the "Fax: " part is actual text so that appears in this
report whether or not there is a fax number. I'm hoping there's
another way to do this, then, that would make the field empty and
shrink when there isn't a fax number in the record.

Is there a way to do this, pls?

Thank you! D

This might work;
Iif(IsNull([FaxNumber])=False,"Fax: " & Trim([FaxNumber]),"")

 

[StargateFan]

A small modification to this:
=IIf(IsNull([FaxNumber])=False,"Fax: " & Trim([FaxNumber]),Null)

Actually, Duane, your code like this in your earlier msg:

="Fax: " + [FaxNumber]

worked perfectly! Is there any reason to use the above in that case?

************************************************************
In fact, I then noticed that the telephone number wasn't displaying
correctly and I'd missed that before. The difficulty is that there
are more concatenated fields involved than in the fax one.

It took about 3 minutes of playing with the signs and switching "+"
and "&" but I got the results needed and this seems to work for the
telephone field. This phone field has the same problem with text
header but with additional complication of an extension # field that
isn't used always. This is what seems to be working for this phone
field:

="Tel: "+[TelephoneNumber] & ", #"+[ExtensionOrBusStopNo]

If there is no telephone number, which is sometimes the case, no
telephone # appears and no extra line shows in the report so the
CanShrink works there.

If there is a phone but without extension # in a record, just the
"Tel: " and actual phone # appear with no additional text.

When there is something in extension # field, extension is shown along
with phone along with the ", #"!

This is just toooo kewl for words!!! Thanks. D

--
Duane Hookom
MS Access MVP
--

I've used CanShrink in all fields and in the section. Everything
shrinks beautifully. However, one line that deals with a field for
fax numbers has a text header that looks like this:

=Trim("Fax: " & [FaxNumber])

Of course, the "Fax: " part is actual text so that appears in this
report whether or not there is a fax number. I'm hoping there's
another way to do this, then, that would make the field empty and
shrink when there isn't a fax number in the record.

Is there a way to do this, pls?

Thank you! D

This might work;
Iif(IsNull([FaxNumber])=False,"Fax: " & Trim([FaxNumber]),"")

 

[StargateFan]

I've used CanShrink in all fields and in the section. Everything
shrinks beautifully. However, one line that deals with a field for
fax numbers has a text header that looks like this:

=Trim("Fax: " & [FaxNumber])

Of course, the "Fax: " part is actual text so that appears in this
report whether or not there is a fax number. I'm hoping there's
another way to do this, then, that would make the field empty and
shrink when there isn't a fax number in the record.

Is there a way to do this, pls?

Thank you! D

This might work;
Iif(IsNull([FaxNumber])=False,"Fax: " & Trim([FaxNumber]),"")

Thank you! I'll test this out, too.

 

[Duane Hookom]

I would just go with the functionality of the + and & operators. It might be
a little obscure but it is, as you found "Kewl".

--
Duane Hookom
MS Access MVP
--

A small modification to this:
=IIf(IsNull([FaxNumber])=False,"Fax: " & Trim([FaxNumber]),Null)

Actually, Duane, your code like this in your earlier msg:

="Fax: " + [FaxNumber]

worked perfectly! Is there any reason to use the above in that case?

************************************************************
In fact, I then noticed that the telephone number wasn't displaying
correctly and I'd missed that before. The difficulty is that there
are more concatenated fields involved than in the fax one.

It took about 3 minutes of playing with the signs and switching "+"
and "&" but I got the results needed and this seems to work for the
telephone field. This phone field has the same problem with text
header but with additional complication of an extension # field that
isn't used always. This is what seems to be working for this phone
field:

="Tel: "+[TelephoneNumber] & ", #"+[ExtensionOrBusStopNo]

If there is no telephone number, which is sometimes the case, no
telephone # appears and no extra line shows in the report so the
CanShrink works there.

If there is a phone but without extension # in a record, just the
"Tel: " and actual phone # appear with no additional text.

When there is something in extension # field, extension is shown along
with phone along with the ", #"!

This is just toooo kewl for words!!! Thanks. D

--
Duane Hookom
MS Access MVP
--

StargateFan wrote:
I've used CanShrink in all fields and in the section. Everything
shrinks beautifully. However, one line that deals with a field for
fax numbers has a text header that looks like this:

=Trim("Fax: " & [FaxNumber])

Of course, the "Fax: " part is actual text so that appears in this
report whether or not there is a fax number. I'm hoping there's
another way to do this, then, that would make the field empty and
shrink when there isn't a fax number in the record.

Is there a way to do this, pls?

Thank you! D
This might work;
Iif(IsNull([FaxNumber])=False,"Fax: " & Trim([FaxNumber]),"")

 

[Marshall Barton]

A small modification to this:
=IIf(IsNull([FaxNumber])=False,"Fax: " & Trim([FaxNumber]),Null)

Actually, Duane, your code like this in your earlier msg:

="Fax: " + [FaxNumber]

worked perfectly! Is there any reason to use the above in that case?

************************************************************
In fact, I then noticed that the telephone number wasn't displaying
correctly and I'd missed that before. The difficulty is that there
are more concatenated fields involved than in the fax one.

It took about 3 minutes of playing with the signs and switching "+"
and "&" but I got the results needed and this seems to work for the
telephone field. This phone field has the same problem with text
header but with additional complication of an extension # field that
isn't used always. This is what seems to be working for this phone
field:

="Tel: "+[TelephoneNumber] & ", #"+[ExtensionOrBusStopNo]

If there is no telephone number, which is sometimes the case, no
telephone # appears and no extra line shows in the report so the
CanShrink works there.

If there is a phone but without extension # in a record, just the
"Tel: " and actual phone # appear with no additional text.

When there is something in extension # field, extension is shown along
with phone along with the ", #"!

This is just toooo kewl for words!!! Thanks. D

It is quick and concise and I use it all the time. However,
I use parenthesis to make sure I can easily see what the &
and + are operating on.

=("Tel: "+[TelephoneNumber])&(", #"+[ExtensionOrBusStopNo])

 

[StargateFan]

I would just go with the functionality of the + and & operators. It might be
a little obscure but it is, as you found "Kewl".

Awesome! Using + and & is simple and straightforward and I can modify
to any situation whereas the null one would be difficult for me to
work with.

Thanks.

--
Duane Hookom
MS Access MVP
--

A small modification to this:
=IIf(IsNull([FaxNumber])=False,"Fax: " & Trim([FaxNumber]),Null)

Actually, Duane, your code like this in your earlier msg:

="Fax: " + [FaxNumber]

worked perfectly! Is there any reason to use the above in that case?

************************************************************
In fact, I then noticed that the telephone number wasn't displaying
correctly and I'd missed that before. The difficulty is that there
are more concatenated fields involved than in the fax one.

It took about 3 minutes of playing with the signs and switching "+"
and "&" but I got the results needed and this seems to work for the
telephone field. This phone field has the same problem with text
header but with additional complication of an extension # field that
isn't used always. This is what seems to be working for this phone
field:

="Tel: "+[TelephoneNumber] & ", #"+[ExtensionOrBusStopNo]

If there is no telephone number, which is sometimes the case, no
telephone # appears and no extra line shows in the report so the
CanShrink works there.

If there is a phone but without extension # in a record, just the
"Tel: " and actual phone # appear with no additional text.

When there is something in extension # field, extension is shown along
with phone along with the ", #"!

This is just toooo kewl for words!!! Thanks. D

--
Duane Hookom
MS Access MVP
--

StargateFan wrote:
I've used CanShrink in all fields and in the section. Everything
shrinks beautifully. However, one line that deals with a field for
fax numbers has a text header that looks like this:

=Trim("Fax: " & [FaxNumber])

Of course, the "Fax: " part is actual text so that appears in this
report whether or not there is a fax number. I'm hoping there's
another way to do this, then, that would make the field empty and
shrink when there isn't a fax number in the record.

Is there a way to do this, pls?

Thank you! D
This might work;
Iif(IsNull([FaxNumber])=False,"Fax: " & Trim([FaxNumber]),"")

 

[StargateFan]

A small modification to this:
=IIf(IsNull([FaxNumber])=False,"Fax: " & Trim([FaxNumber]),Null)

Actually, Duane, your code like this in your earlier msg:

="Fax: " + [FaxNumber]

worked perfectly! Is there any reason to use the above in that case?

************************************************************
In fact, I then noticed that the telephone number wasn't displaying
correctly and I'd missed that before. The difficulty is that there
are more concatenated fields involved than in the fax one.

It took about 3 minutes of playing with the signs and switching "+"
and "&" but I got the results needed and this seems to work for the
telephone field. This phone field has the same problem with text
header but with additional complication of an extension # field that
isn't used always. This is what seems to be working for this phone
field:

="Tel: "+[TelephoneNumber] & ", #"+[ExtensionOrBusStopNo]

If there is no telephone number, which is sometimes the case, no
telephone # appears and no extra line shows in the report so the
CanShrink works there.

If there is a phone but without extension # in a record, just the
"Tel: " and actual phone # appear with no additional text.

When there is something in extension # field, extension is shown along
with phone along with the ", #"!

This is just toooo kewl for words!!! Thanks. D

It is quick and concise and I use it all the time. However,
I use parenthesis to make sure I can easily see what the &
and + are operating on.

=("Tel: "+[TelephoneNumber])&(", #"+[ExtensionOrBusStopNo])

Oh that is a good idea! I'm all for it. I'll modify my code to the
above.