Formatting lost on part of concatenated field of report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RayO - 25 Oct 2005 20:50 GMT

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?

Reply to this Message

Marshall Barton - 25 Oct 2005 21:40 GMT

>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, ???).

Signature

Marsh
MVP [MS Access]

Reply to this Message

RayO - 25 Oct 2005 21:51 GMT

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
[quoted text clipped - 16 lines]
> phone number. Also need to know the phone number field type
> in the table (Text, Long, ???).

Reply to this Message

Marshall Barton - 26 Oct 2005 00:24 GMT

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])

Signature

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
[quoted text clipped - 24 lines]
>> phone number. Also need to know the phone number field type
>> in the table (Text, Long, ???).

Reply to this Message

RayO - 26 Oct 2005 15:41 GMT

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
[quoted text clipped - 40 lines]
> >> phone number. Also need to know the phone number field type
> >> in the table (Text, Long, ???).

Reply to this Message

Marshall Barton - 26 Oct 2005 16:53 GMT

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,
"(@@@) @@@-@@@@"))

Signature

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
[quoted text clipped - 54 lines]
>> >> phone number. Also need to know the phone number field type
>> >> in the table (Text, Long, ???).

Reply to this Message

RayO - 26 Oct 2005 17:01 GMT

It works!! You're the greatest!!

Reply to this Message