Phone number entry

[Will Pittenger]

I expect to copy and paste (and sometimes drag and drop) phone numbers into
a phone number formatted field. Is it possible to have the Input format
adjust to the source format and translate the phone number into the field's
mask? I normally use ###-###-####. However, some phone numbers are
formatted (###) ###-####, ###.###.####, ### ### ####, or some combination of
those formats. As is, I have to copy the phone number over piece by piece
until I have the entire number. I would prefer to copy the number with one
pass.

Also, when dragging and dropping phone numbers, I would like the area code
defaulted, in my case to 503. That way, if the text value is just 345-3434,
the field gets set to 503-345-3434.

 

[Mike Painter]

I expect to copy and paste (and sometimes drag and drop) phone
numbers into a phone number formatted field. Is it possible to have
the Input format adjust to the source format and translate the phone
number into the field's mask? I normally use ###-###-####. However,
some phone numbers are formatted (###) ###-####, ###.###.####, ###
### ####, or some combination of those formats. As is, I have to
copy the phone number over piece by piece until I have the entire
number. I would prefer to copy the number with one pass.

Also, when dragging and dropping phone numbers, I would like the area
code defaulted, in my case to 503. That way, if the text value is
just 345-3434, the field gets set to 503-345-3434.

Drag and drop is not supported in Access.

A field mask is different from a field format and I suspect you are
interested in the latter.

It is usually better to use a text field and @@@ @@@-@@@@. This is
especially true if you are pasting non numeric characters.
that way 3451234 formats as 345-1234 and the leading "-" does not show.

If you paste a value in that contains any characters you do not want, you
will have to write code to strip those characters. The Replace function
would be ideal for this.
You could then check to see if the string was 7 characters long and add 503
to it if needed.

 

[Will Pittenger]

Sorry, wrong placeholder. Try 000\-000\-0000.

I can drag text that I select in IE to my field in Access. It acts like I
typed it my self.

How do I put that code into Access?

 

[Mike Painter]

Sorry, wrong placeholder. Try 000\-000\-0000.

I can drag text that I select in IE to my field in Access. It acts
like I typed it my self.

How do I put that code into Access?

The replace function is described in help. It would appear in probably the
on exit event.

 

[Will Pittenger]

I was just looking at it. Access is asking whether I want to use the
"Expression Builder", "Macro Builder", or the "Code Builder". I used the
expression builder for other things. Still, for this type of thing, would
the others be better? Also, can Replace strip out multiple characters at
once? MFC's CString (and probably .NET if I were to check) allow find and
replace of strings like "() ." with "-". In this case, I think I need two
calls to Replace as the parentheses would not be replaced with dashes.
Either that or I would remove all such characters entirely and let the input
mask format the string.

 

[Mike Painter]

I was just looking at it. Access is asking whether I want to use the
"Expression Builder", "Macro Builder", or the "Code Builder". I used
the expression builder for other things. Still, for this type of
thing, would the others be better? Also, can Replace strip out
multiple characters at once? MFC's CString (and probably .NET if I
were to check) allow find and replace of strings like "() ." with
"-". In this case, I think I need two calls to Replace as the
parentheses would not be replaced with dashes. Either that or I would
remove all such characters entirely and let the input mask format the
string. ----------

Clearly this is not a macro and the expression builder is used to build
expressions.
You should have the option of "event procedure" and that is what this is.

I don't know how Replace handles the replacement you give as an example.
Try it and find out.

I suspect it will do as your example but you need each item in a string such
as "().-" replaced item by item. It would be easy to build a function that
did this but Pick Basic is the only Basic I know that has it.

 

[John Vinson]

Either that or I would remove all such characters entirely and let the input
mask format the string.

That's what I would suggest. You can use three or four Update queries,
one for each character - update Phone to

Replace([Phone], "(", "")

and similarly for the other punctuation, including blank.

John W. Vinson[MVP]
(no longer chatting for now)

 

[Will Pittenger]

I think that I would still prefer something like shown below. It would use
the second string as a set, not a substring.

RemoveCharFromString([field], "(). ")

 

[Mike Painter]

I think that I would still prefer something like shown below. It
would use the second string as a set, not a substring.

RemoveCharFromString([field], "(). ")

Then you will have to switch to Pick Basic and use Convert, write it
yourself or get somebody to do it for you.

PickConvert( String, "abc", "12")

If string is "a bad boy calls" the result should be "1 21d 2oy 1lls"

a matches and is replaced by 1.
b matches and is replaced by 2.
c has no match so is removed.

Replace makes it easy to do.