Results 1 to 6 of 6
  1. #1
    Konstantinos is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    1

    Phone Numbers Query

    Hi there,

    i have a table called Customers... in this table exists a field called "mobile number"... Normally this field should contain ONLY 10 numbers...

    but due to bad design there are a lot of fields that are less than 10 numbers...

    my question is how to make a query asking to see only those fields that are less than 10 nymbers

    thanks in advance

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try this query:

    Code:
    Select * from Customers
    WHERE
    len([mobile number])<10
    Good luck

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Use this Criteria:
    Code:
    WHERE (LEN([mobile number])<10) OR ([mobile number] Is Null);
    Note: The Is Null piece is required to pick up blank/empty entries.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Good point Joe, NULL might work but I'd try this
    Code:
    WHERE (LEN([mobile number])<10) OR 
          (LEN([mobile number] & "") = 0);

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Good point Joe, NULL might work but I'd try this
    Yep, it worked on the records I tested, but I do like your revised method too.
    Access handles Nulls and empty strings (blanks) differently, so that pretty much covers all bases.

    BTW, you can simplify your last one to just this:
    Code:
    WHERE LEN([mobile number] & "")<10;

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

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

Similar Threads

  1. Mask for UK phone numbers
    By Jen0dorf in forum Access
    Replies: 4
    Last Post: 07-21-2015, 12:06 PM
  2. How to reformat phone numbers in a query
    By lylyming977 in forum Queries
    Replies: 4
    Last Post: 03-27-2015, 04:37 PM
  3. Displaying phone numbers
    By MaineLady in forum Access
    Replies: 2
    Last Post: 10-19-2014, 03:27 PM
  4. Excluding phone numbers using a query
    By Scott O'Neal in forum Queries
    Replies: 1
    Last Post: 09-06-2012, 11:04 AM
  5. Verifying phone numbers
    By looloo in forum Forms
    Replies: 15
    Last Post: 09-23-2011, 07:38 AM

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