Formatting lost on part of concatenated field of report

[Guest]

I am using "=Format([txtCustTN1],"(000) 000-0000") & " / " & [txtCustTN2]"
in a report to display the telephone number from two fields (txtCustTN1 and
txtCustTN2). It works fine as long as there are two numbers. My problem is
getting "txtCustTN1" to display properly when there is no "txtCustTN2." For
example, it will appear as "(383) 561(-22) 99 - /" How can I fix this?

 

[Marshall Barton]

I am using "=Format([txtCustTN1],"(000) 000-0000") & " / " & [txtCustTN2]"
in a report to display the telephone number from two fields (txtCustTN1 and
txtCustTN2). It works fine as long as there are two numbers. My problem is
getting "txtCustTN1" to display properly when there is no "txtCustTN2." For
example, it will appear as "(383) 561(-22) 99 - /"

I have no idea where the ( ) around -22 could possibly come
from.

If that was just a typo, try using:

=Format([txtCustTN1],"(000) 000-0000") & (" / " +
[txtCustTN2])

which presumes that "no txtCustTN2" means that it is Null

If that doesn't take care of it, then please provide more
details about what is in those fields when they don't have a
phone number. Also need to know the phone number field type
in the table (Text, Long, ???).

 

[Guest]

I copied and pasted your expression and received an "invalid syntax" error.
In any case here is more info. The second telephone number field is not
required, only the first one but if there is a second number it needs to
appear on the report and the form. Both are text fields with a fixed length
of 15 characters. And no, the ( ) around the -22 is not a typo. That is
the resulting format when there is only one number being reported from the
txtCustTN1 field.

I am using "=Format([txtCustTN1],"(000) 000-0000") & " / " & [txtCustTN2]"
in a report to display the telephone number from two fields (txtCustTN1 and
txtCustTN2). It works fine as long as there are two numbers. My problem is
getting "txtCustTN1" to display properly when there is no "txtCustTN2." For
example, it will appear as "(383) 561(-22) 99 - /"

I have no idea where the ( ) around -22 could possibly come
from.

If that was just a typo, try using:

=Format([txtCustTN1],"(000) 000-0000") & (" / " +
[txtCustTN2])

which presumes that "no txtCustTN2" means that it is Null

If that doesn't take care of it, then please provide more
details about what is in those fields when they don't have a
phone number. Also need to know the phone number field type
in the table (Text, Long, ???).

 

[Marshall Barton]

Ok, that helps. I still have no idea where those
parenthesis are coming from, but I also don't know how you
could get anything by using a numeric format on a text
string. Try using:

=Format([txtCustTN1],"(@@@) @@@-@@@@") & (" / " +
[txtCustTN2]

You do need to verify that the table field, txtCustTN2, has
its AllowZeroLength property to No, because the above only
works if txtCustTN2 is Null when there is no phone number.

If that still causes trouble, try this:

=Format([txtCustTN1],"(@@@) @@@-@@@@") &
IIf(Nz([txtCustTN2], "") = "", "", " / " & [txtCustTN2])
--
Marsh
MVP [MS Access]

I copied and pasted your expression and received an "invalid syntax" error.
In any case here is more info. The second telephone number field is not
required, only the first one but if there is a second number it needs to
appear on the report and the form. Both are text fields with a fixed length
of 15 characters. And no, the ( ) around the -22 is not a typo. That is
the resulting format when there is only one number being reported from the
txtCustTN1 field.

I am using "=Format([txtCustTN1],"(000) 000-0000") & " / " & [txtCustTN2]"
in a report to display the telephone number from two fields (txtCustTN1 and
txtCustTN2). It works fine as long as there are two numbers. My problem is
getting "txtCustTN1" to display properly when there is no "txtCustTN2." For
example, it will appear as "(383) 561(-22) 99 - /"

I have no idea where the ( ) around -22 could possibly come
from.

If that was just a typo, try using:

=Format([txtCustTN1],"(000) 000-0000") & (" / " +
[txtCustTN2])

which presumes that "no txtCustTN2" means that it is Null

If that doesn't take care of it, then please provide more
details about what is in those fields when they don't have a
phone number. Also need to know the phone number field type
in the table (Text, Long, ???).

 

[Guest]

Unfortunately, both expressions rendered a result of: "(852() 9) 63--8221"
for txtCustTN1 (this is different TN obviously). And yes, AllowZeroLength
property for txtCustTN2 was set to "No."

Now I'm wondering if the expression is not working with the format property
in the report for this concatenated field. In the format properties I have
"(@@@) @@@-@@@@." (Both fields have this format property in the table as
well.) If I remove it in the report, txtCustTN1 appears correctly formatted
fine as a single number in the report but when two numbers are reported,
txtCustTN2 is not formatted; the report then shows "(913) 234-1234 /
8529638221" using either of your suggested expressions. Is that the problem?
If so, what should it be?

Ok, that helps. I still have no idea where those
parenthesis are coming from, but I also don't know how you
could get anything by using a numeric format on a text
string. Try using:

=Format([txtCustTN1],"(@@@) @@@-@@@@") & (" / " +
[txtCustTN2]

You do need to verify that the table field, txtCustTN2, has
its AllowZeroLength property to No, because the above only
works if txtCustTN2 is Null when there is no phone number.

If that still causes trouble, try this:

=Format([txtCustTN1],"(@@@) @@@-@@@@") &
IIf(Nz([txtCustTN2], "") = "", "", " / " & [txtCustTN2])
--
Marsh
MVP [MS Access]

I copied and pasted your expression and received an "invalid syntax" error.
In any case here is more info. The second telephone number field is not
required, only the first one but if there is a second number it needs to
appear on the report and the form. Both are text fields with a fixed length
of 15 characters. And no, the ( ) around the -22 is not a typo. That is
the resulting format when there is only one number being reported from the
txtCustTN1 field.

RayO wrote:
I am using "=Format([txtCustTN1],"(000) 000-0000") & " / " & [txtCustTN2]"
in a report to display the telephone number from two fields (txtCustTN1 and
txtCustTN2). It works fine as long as there are two numbers. My problem is
getting "txtCustTN1" to display properly when there is no "txtCustTN2." For
example, it will appear as "(383) 561(-22) 99 - /"

I have no idea where the ( ) around -22 could possibly come
from.

If that was just a typo, try using:

=Format([txtCustTN1],"(000) 000-0000") & (" / " +
[txtCustTN2])

which presumes that "no txtCustTN2" means that it is Null

If that doesn't take care of it, then please provide more
details about what is in those fields when they don't have a
phone number. Also need to know the phone number field type
in the table (Text, Long, ???).

 

[Marshall Barton]

Oh my, you must clear the text box's Format property. We're
doing the formatting in the expression and do not want the
text box's formatting to mangle what we've done. If you
were hoping that the format property would apply to an
individual value in the expression, abandon that idea. The
format property is applied to the result of the expression,
which finally explains where the extra parenthesis are
coming from.

Just use the same format function on the second number:

=Format(txtCustTN1, "(@@@) @@@-@@@@") &
IIf(txtCustTN2 Is Null, "", " / " & Format(txtCustTN2,
"(@@@) @@@-@@@@"))
--
Marsh
MVP [MS Access]

Unfortunately, both expressions rendered a result of: "(852() 9) 63--8221"
for txtCustTN1 (this is different TN obviously). And yes, AllowZeroLength
property for txtCustTN2 was set to "No."

Now I'm wondering if the expression is not working with the format property
in the report for this concatenated field. In the format properties I have
"(@@@) @@@-@@@@." (Both fields have this format property in the table as
well.) If I remove it in the report, txtCustTN1 appears correctly formatted
fine as a single number in the report but when two numbers are reported,
txtCustTN2 is not formatted; the report then shows "(913) 234-1234 /
8529638221" using either of your suggested expressions. Is that the problem?
If so, what should it be?

Ok, that helps. I still have no idea where those
parenthesis are coming from, but I also don't know how you
could get anything by using a numeric format on a text
string. Try using:

=Format([txtCustTN1],"(@@@) @@@-@@@@") & (" / " +
[txtCustTN2]

You do need to verify that the table field, txtCustTN2, has
its AllowZeroLength property to No, because the above only
works if txtCustTN2 is Null when there is no phone number.

If that still causes trouble, try this:

=Format([txtCustTN1],"(@@@) @@@-@@@@") &
IIf(Nz([txtCustTN2], "") = "", "", " / " & [txtCustTN2])

I copied and pasted your expression and received an "invalid syntax" error.
In any case here is more info. The second telephone number field is not
required, only the first one but if there is a second number it needs to
appear on the report and the form. Both are text fields with a fixed length
of 15 characters. And no, the ( ) around the -22 is not a typo. That is
the resulting format when there is only one number being reported from the
txtCustTN1 field.


RayO wrote:
I am using "=Format([txtCustTN1],"(000) 000-0000") & " / " & [txtCustTN2]"
in a report to display the telephone number from two fields (txtCustTN1 and
txtCustTN2). It works fine as long as there are two numbers. My problem is
getting "txtCustTN1" to display properly when there is no "txtCustTN2." For
example, it will appear as "(383) 561(-22) 99 - /"


:
I have no idea where the ( ) around -22 could possibly come
from.

If that was just a typo, try using:

=Format([txtCustTN1],"(000) 000-0000") & (" / " +
[txtCustTN2])

which presumes that "no txtCustTN2" means that it is Null

If that doesn't take care of it, then please provide more
details about what is in those fields when they don't have a
phone number. Also need to know the phone number field type
in the table (Text, Long, ???).

 

[Guest]

It works!! You're the greatest!!

Ok, that helps. I still have no idea where those
parenthesis are coming from, but I also don't know how you
could get anything by using a numeric format on a text
string. Try using:

=Format([txtCustTN1],"(@@@) @@@-@@@@") & (" / " +
[txtCustTN2]

You do need to verify that the table field, txtCustTN2, has
its AllowZeroLength property to No, because the above only
works if txtCustTN2 is Null when there is no phone number.

If that still causes trouble, try this:

=Format([txtCustTN1],"(@@@) @@@-@@@@") &
IIf(Nz([txtCustTN2], "") = "", "", " / " & [txtCustTN2])
--
Marsh
MVP [MS Access]

I copied and pasted your expression and received an "invalid syntax" error.
In any case here is more info. The second telephone number field is not
required, only the first one but if there is a second number it needs to
appear on the report and the form. Both are text fields with a fixed length
of 15 characters. And no, the ( ) around the -22 is not a typo. That is
the resulting format when there is only one number being reported from the
txtCustTN1 field.

RayO wrote:
I am using "=Format([txtCustTN1],"(000) 000-0000") & " / " & [txtCustTN2]"
in a report to display the telephone number from two fields (txtCustTN1 and
txtCustTN2). It works fine as long as there are two numbers. My problem is
getting "txtCustTN1" to display properly when there is no "txtCustTN2." For
example, it will appear as "(383) 561(-22) 99 - /"

I have no idea where the ( ) around -22 could possibly come
from.

If that was just a typo, try using:

=Format([txtCustTN1],"(000) 000-0000") & (" / " +
[txtCustTN2])

which presumes that "no txtCustTN2" means that it is Null

If that doesn't take care of it, then please provide more
details about what is in those fields when they don't have a
phone number. Also need to know the phone number field type
in the table (Text, Long, ???).