Autoexpand on a phone input mask

[BFish]

I didn't think this would be as hard as it is but after what I think has been
a thorough search of previous postings I have not found any information on a
Phone field with an input mask and being able to have autoexpand feature find
a record in a table.

I would like to have a user type only the phone data (ie. "(", ")" and "-")
but still be readable as a standard USA format of
" (@@@) @@@-@@@@ ". I have the the combo box input mask set to " !\(999")
"000\-0000;;# " and the dropdown row source sql:

SELECT tblPhone.PhoneID, Format([Phone],"(@@@)@@@-@@@@") AS NewPhone FROM
tblPhone ORDER BY tblPhone.Phone;

And of course the reason for this post is there any possiblity of a work
around to be able to use the autoexpand with these circumstances. I do
believe I have exhausted all settings between formatting and input mask
settings.

Thank you for any help.

Bill Fischer

 

[John Nurick]

Hi Bill,

It looks as if you're currently storing only the digits of the number
rather than the formatted number, e.g. 7895551212 instead of (789)
555-1212. Perhaps you're even storing the phone "numbers" in a numeric
field, which is only a good idea if you need to use them for arithmetic.

If you (1) set up the input mask on the data entry textbox so the
non-numeric characters are stored in the field (use an update query if
necessary to add them to the existing records) and (2) remove any input
mask from the combo box and set its RowSource to
SELECT PhoneID, Phone from tblPhone ORDER BY Phone;
the autoexpand will work normally (i.e. if the user types
(4
it will find the first phone number beginning with "(4"

If you want the combo to display the formatting characters but the
autoexpand to work if the user only types the digits, I think you'll
have to do some cunning programming with the combo's Change event and
Text property: sooner you than me!

I didn't think this would be as hard as it is but after what I think has been
a thorough search of previous postings I have not found any information on a
Phone field with an input mask and being able to have autoexpand feature find
a record in a table.

I would like to have a user type only the phone data (ie. "(", ")" and "-")
but still be readable as a standard USA format of
" (@@@) @@@-@@@@ ". I have the the combo box input mask set to " !\(999")
"000\-0000;;# " and the dropdown row source sql:

SELECT tblPhone.PhoneID, Format([Phone],"(@@@)@@@-@@@@") AS NewPhone FROM
tblPhone ORDER BY tblPhone.Phone;

And of course the reason for this post is there any possiblity of a work
around to be able to use the autoexpand with these circumstances. I do
believe I have exhausted all settings between formatting and input mask
settings.

Thank you for any help.

Bill Fischer

 

[BFish via AccessMonster.com]

Hi John,

You are correct the data is stored as digits only but as a text field.

And yes for ease of finding a matching phone record I am looking for digit
only entry with the formated control. Cake and eat it too comes to mind.

I had all but guessed programming would be my only option, hoping someone had
an easy solution to what looks like might be a considerable effort.

Thank you for your reply John.

Bill