format phone field

[fishqqq]

I have a field that stores phone numbers and is sometimes null
I have the control source as :

="Phone: " & Format([Phone],"(@@@) @@@-@@@@")

which works well but if the field is null it still shows the word
Phone on the report.
Can someone show me how to format this so that if the field is null
then the word Phone will NOT be shown on the report?

tks
Steve

 

[John W. Vinson]

I have a field that stores phone numbers and is sometimes null
I have the control source as :

="Phone: " & Format([Phone],"(@@@) @@@-@@@@")

which works well but if the field is null it still shows the word
Phone on the report.
Can someone show me how to format this so that if the field is null
then the word Phone will NOT be shown on the report?

tks
Steve

Include the literal word phone in the format itself. The \ character makes the
next one literal:

=Format([Phone], "\P\h\o\n\e\: (@@@) @@@-@@@@")

Or perhaps better, just set the control source to [Phone] and set the Format
property of the textbox to the same string.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Arvin Meyer]

Try:

=("Phone: " + Format([Phone],"(@@@) @@@-@@@@"))

The + concatenation operator will not display nulls.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://access.mvps.org
Co-author: "Access Solutions", published by Wiley

 

[fishqqq]

I have a field that stores phone numbers and is sometimes null
I have the control source as :

="Phone: " & Format([Phone],"(@@@) @@@-@@@@")

which works well but if the field is null it still shows the word
Phone on the report.
Can someone show me how to format this so that if the field is null
then the word Phone will NOT be shown on the report?

tks
Steve

Include the literal word phone in the format itself. The \ character makes the
next one literal:

=Format([Phone], "\P\h\o\n\e\: (@@@) @@@-@@@@")

Or perhaps better, just set the control source to [Phone] and set the Format
property of the textbox to the same string.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Thanks John, this works great for the N.American phone number format
but below this i have a field for Int'l Phone Numbers and again I
would like the word "Phone:" NOT to be on the report of the Int'l
Phone field is empty

=Format([intl phone],"""Phone: ""@@@@@@@@@@")

I tried playing with the format you suggested (above) but I couldn't
get anything to work correctly. it either leaves the word Phone in the
field or changes the formatting of the numbers.

Note because the int'l numbers don't really have any FORMAT the user
will enter the Int'l phone sometimes with brackets sometimes with
spaces etc. Can we have the report show the [intl phone] exactly as it
is in the table but add the word "Phone: " infront of it - but only
if there is something in the field otherwise leave the field blank on
the report?

tks
Steve

 

[Arvin Meyer]

I have a field that stores phone numbers and is sometimes null
I have the control source as :

="Phone: " & Format([Phone],"(@@@) @@@-@@@@")

which works well but if the field is null it still shows the word
Phone on the report.
Can someone show me how to format this so that if the field is null
then the word Phone will NOT be shown on the report?

tks
Steve

Include the literal word phone in the format itself. The \ character makes
the
next one literal:

=Format([Phone], "\P\h\o\n\e\: (@@@) @@@-@@@@")

Or perhaps better, just set the control source to [Phone] and set the
Format
property of the textbox to the same string.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see alsohttp://www.utteraccess.com

Thanks John, this works great for the N.American phone number format
but below this i have a field for Int'l Phone Numbers and again I
would like the word "Phone:" NOT to be on the report of the Int'l
Phone field is empty

=Format([intl phone],"""Phone: ""@@@@@@@@@@")

I tried playing with the format you suggested (above) but I couldn't
get anything to work correctly. it either leaves the word Phone in the
field or changes the formatting of the numbers.

Note because the int'l numbers don't really have any FORMAT the user
will enter the Int'l phone sometimes with brackets sometimes with
spaces etc. Can we have the report show the [intl phone] exactly as it
is in the table but add the word "Phone: " infront of it - but only
if there is something in the field otherwise leave the field blank on
the report?
--------------
You can count the characters to determine if the international phone is
ALWAYS different from the 10 characters in US and Canadian phones, and
branch your code accordingly. IOW create a function to do the formatting,
and use the function in the query expression. Again, using the "+"
concatenation operator will allow you to include or ignore the word hone"
based on the existance of a phone number.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://access.mvps.org
Co-author: "Access Solutions", published by Wiley