Prev: updatable query
Next: How to update data in a query
From: Kathy R. on 3 Apr 2010 21:30
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.
From: John Spencer on 4 Apr 2010 08:29
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. wrote:
> 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.
From: Kathy R. on 4 Apr 2010 14:29
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.

John Spencer wrote:
> 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. wrote:
>> 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.
 | 
Pages: 1
Prev: updatable query
Next: How to update data in a query