Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ListBox and Imput Mask (2002)

    I have a control Tel-Work with an imput mask !(999") "000-0000.
    In the list box lstVendors the telephone number is 4155551212 with no dashes or parenteses. Is there anyway for both.
    i.e. (415) 555-1212 in the list box?

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ListBox and Imput Mask (2002)

    Try using the Format property - see Helpfiles for the specifics.

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: ListBox and Input Mask (2002)

    The problem is the input mask you are using is not saving symbols along with data. That's why listbox only display the numbers. Also, the input mask does not require area code to be entered. This can lead to problems too. Where I live (Wash DC area) even local calls require an area code. What I would do is update table so data is saved with symbols, and replace this input mask:
    <pre>!(999") "000-0000;;_</pre>

    with this one: <pre>!(000") "000-0000;0;_</pre>

    The latter input mask will save symbols along with data and require you to enter an area code. Of course you would have to update existing data to be consistent with new format. If updating existing data is not an option, to solve problem with listbox you will need to modify query or SQL used for listbox's Row Source. First column (bound column) will be actual field in table. 2nd column will be calculated field that displays phone no in proper format. The syntax for 2nd column depends on whether or not any of your phone numbers do not have area code entered. If so, the syntax is more convoluted (another good reason for requiring area codes).

    Sample SQL for query (all phone no's have area code):

    SELECT Phone, "(" & Left([Phone],3) & ") " & Mid([Phone],4,3) & "-" & Right([Phone],4) AS PhoneNo
    FROM Table4
    WHERE (((Phone) Is Not Null))
    ORDER BY Phone;

    In above "Phone" is name of Phone no field, "PhoneNo" is alias for calculated field.
    If not all phone no's have area code entered need to modify query SQL:

    SELECT Phone, IIf(Len([Phone])=10,"(" & Left([Phone],3) & ") " & Mid([Phone],4,3) & "-" & Right([Phone],4),Left([Phone],3) & "-" & Right([Phone],4)) AS PhoneNo
    FROM Table4
    WHERE ((Phone) Is Not Null)
    ORDER BY Phone;

    Using this query as Row Source for listbox, set column count to 2 and set width of first column to 0" to hide first column, so only formatted phone numbers appear. If you update table as recommended you can avoid having to use this convoluted syntax just to display a phone number in a list. You can update table with update query by simply using one of the same expressions used above for formatting purposes to update existing data (w/o symbols) to new format.

    HTH

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Opps! And thanks that did it!

    Opps! And thanks that did it!
    I thought that 0 at the end was just extra stuff and deleted it when I put it as the imput mask-Opps!
    Thanks for point the error of my ways and your extensive code.
    Frank <img src=/S/woops.gif border=0 alt=woops width=58 height=36>

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: ListBox and Input Mask (2002)

    On second thought, as MarkJ suggested, you can use Format function to format phone number, this would be a lot simpler than my previous suggestion. Example of syntax:

    <pre>Format([Phone],"(@@@) &&&-&&&&")</pre>

    Though be advised, if area code not entered, result will look like this:
    <pre>( ) 123-4567</pre>

    If you don't want empty parentheses to appear you'd have to use conditional expression as shown in previous example, modifying format expression for 7 digit phone numbers w/o area codes. Ex:
    <pre>Format([Phone],"&&&-&&&&")</pre>


    HTH

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    San Francisco, CA, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks, Thanks again!

    Thanks, Thanks again!
    Frank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •