Results 1 to 10 of 10
  1. #1
    Ajay is offline Novice
    Windows 7 Access 2003
    Join Date
    Mar 2010
    Location
    U.S.A.
    Posts
    4

    Phone number in (000) 000-0000

    I am sure this has been done before...
    I need to query a Clients table to identify, if a client exists by querying the phone number field. The phone numbers are stored in the table using the input mask:
    !\(999") "000\-0000;0;_
    How do I write the criteria in the query following the prompt: [Enter client phone number:]
    ?
    The query succeeds only if I enter well formatted phone number e.g. (987) 654-3210.
    I would prefer that the end-user type the phone number into a template, so he can type only the digits, without the need to type using the exact format.
    Thanks for the help.

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Create a query and in the Criteria of of phone number type the following code:
    Like [Enter client phone number:]+"*"

    This will solve your problem. You can type the number with out '()' and '-'or this will work even if you want to do a part search.
    if this solves your problem mark this thread solved.

  3. #3
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    is your problem solved.

  4. #4
    Ajay is offline Novice
    Windows 7 Access 2003
    Join Date
    Mar 2010
    Location
    U.S.A.
    Posts
    4
    Sorry Maximus, about the delay in my reply.

    No it did not work. I had already tried your suggested before adding this thread to the forum. I even cut-n-pasted your suggested text in the criteria row under the PhoneNumber field in the query. It works only, if I enter e.g. (987
    and this gives me many phone numbers beginning with (987) ....
    but it gives me a blank result, if I enter the digits in any other way like e.g. 9876543210 or 987 654 3210

    While test data entry into the PhoneNumber field in the Clients table, I did get the proper template i.e.: (___) ___-____ and thus I was able to type in just the digits.

    May be you can think of another way. Thanks.

  5. #5
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931

    phone number

    Now let me quote what you had written in your First Thread. "I would prefer that the end-user type the phone number into a template, so he can type only the digits, without the need to type using the exact format" i.e. if a phone number is in the format (987) 654-3210 you want the user to type 9876543210 not (987 654 3210 or (987) 654 3210 or 987 654-3210). I am attaching a sample mdb which has only one table EXP and one Query EXP Query. Open the table see the Phone Numbers the Go to Queries and Open the Query Type one of thos phone numbers without () or space in between. Tell me if it works. If not I will think of another way and I would realy appreciate if you will answer quickly.

  6. #6
    Ajay is offline Novice
    Windows 7 Access 2003
    Join Date
    Mar 2010
    Location
    U.S.A.
    Posts
    4

    IT WORKED! but...

    Many thanks Maximus for your patience and offer to follow up further. Your solution worked.

    However, I must mention for your information something strange is going on in my version of MS Access 2003 (11.8166.8172) SP3
    You will note that in my original post I had stated that the input mask that Access gave me was thus: !\(999") "000\-0000;0;_

    In the example zip file, you kindly provided, the input mask is thus: !\(999") "000\-0000;;_

    I find that in my Clients table, mysteriously Access inserted a zero between the two semicolons at the end. i cannot figure why. In fact, I deleted the mask and went back to the wizard to recreate the mask. I was given a mask with the zero again.

    I think that is quite strange. I am a novice at this and have not yet figured how such masks work. I guess, this weird behavior of Access is something for the experts like you to ponder!

    Thanks a zillion for your help.

  7. #7
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Now this calls for creating our own input mask. Open the table. in the in the input mask of the telephone number type: \(###") "###\-####;_ and see if you have the same problem.

  8. #8
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    have you solved your problem if yes mark this thread solved

  9. #9
    Ajay is offline Novice
    Windows 7 Access 2003
    Join Date
    Mar 2010
    Location
    U.S.A.
    Posts
    4

    Thumbs up Solved

    Thanks Maximus.
    Sorry. Not sure how to mark it "Solved"!


    Novice. ughh!

  10. #10
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Please mark this thread solved as this will help others having the same problem find answers quickly. Go to thread tool option and mark the thread solved. if you still have problems follow the link and learn how to mark a thread solved. Glad could help you out.

    https://www.accessforums.net/forum-s...lved-1828.html

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Making New Record Number Next Numerical Number
    By jhillbrown in forum Access
    Replies: 1
    Last Post: 03-10-2010, 11:06 AM
  2. Dialing Phone Numbers From Access
    By obrmb in forum Forms
    Replies: 0
    Last Post: 06-11-2009, 12:35 PM
  3. Replies: 1
    Last Post: 03-12-2009, 09:55 AM
  4. Invoice Number
    By rzwoo in forum Access
    Replies: 0
    Last Post: 02-25-2009, 01:30 AM
  5. Sum Of Positive Number and Negative Number
    By maysamab in forum Reports
    Replies: 1
    Last Post: 10-20-2008, 04:06 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums