formatting a phone number in a string

[Kathy R.]

Hi Folks!

I'm trying to put together a string with two fields - [address] and
[landline], both text fields, the "landline" is an unformatted phone
number.

Using =([address] & ", " & [landline])
gives me:
123 Main Street, 5855551234

I'd like to format the phone number with the mask
!(999) 000-0000;;_
so that it looks like
123 Main Street, (585)555-1234

or if there isn't an area code
123 Main Street, ( )555-1234

Is there a way to do this? Your help would be very much appreciated.

Kathy R.

 

[John Spencer]

Assuming that your field is a text field you can try using the format function
with the format argument.

= ([address] & ", " & Format([landline],"(@@@) @@@-@@@@"))

IF that does not work for you then you will still need the format function but
you will have to test the length of the landline value to determine which
format to apply.

The input mask controls input of data and its arguments are different than the
arguments used by the format function. The format function controls how
values are displayed.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Kathy R.]

Thank you John! That worked perfectly.

Part of my problem is not knowing quite the right terminology (input
mask vs. format), so I have a hard time finding things in the help. I
appreciate your pointers and have now read up on both formats and masks.
I will conquer this one step at a time!

Kathy R.

Assuming that your field is a text field you can try using the format
function with the format argument.

= ([address] & ", " & Format([landline],"(@@@) @@@-@@@@"))

IF that does not work for you then you will still need the format
function but you will have to test the length of the landline value to
determine which format to apply.

The input mask controls input of data and its arguments are different
than the arguments used by the format function. The format function
controls how values are displayed.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi Folks!

I'm trying to put together a string with two fields - [address] and
[landline], both text fields, the "landline" is an unformatted phone
number.

Using =([address] & ", " & [landline])
gives me:
123 Main Street, 5855551234

I'd like to format the phone number with the mask
!(999) 000-0000;;_
so that it looks like
123 Main Street, (585)555-1234

or if there isn't an area code
123 Main Street, ( )555-1234

Is there a way to do this? Your help would be very much appreciated.

Kathy R.