formatting a phone number in a string

formatting a phone number in a string

Post by Kathy R » Mon, 05 Apr 2010 10:30:48

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

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.

formatting a phone number in a string

Post by John Spenc » Mon, 05 Apr 2010 21:29:18

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


formatting a phone number in a string

Post by Kathy R » Tue, 06 Apr 2010 03:29:06

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.