Results 1 to 8 of 8
  1. #1
    pattrickcolin is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Location
    southend on sea
    Posts
    44

    Query with Nulls, Blanks, And Data. Extracting Result with No Full Stop.

    My Query field contains Nulls, Blanks, and data. This data may include a full stop (in any position).
    I need to extract all records that have no full stop.


    Using Not Like "*.* in the criteria, but it doesn't return blanks records.
    Tearing my hair out as I cannot seem to crack this.
    Any ideas.

  2. #2
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    Not sure what you mean by "full stop".

    To find NULL value, the criteria is "Is Null" or "Is Not Null".
    To find Blank value, the criteria is "".

    Not sure what you trying to accomplish. Using the "And" or "Or" to find data for "Null" value or "Blank" Value.
    Criteria: WHERE FINDDATA Is Null Or FINDDATA Like "*Something*"

    Hope this helps.

  3. #3
    pattrickcolin is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Location
    southend on sea
    Posts
    44
    Quote Originally Posted by lfpm062010 View Post
    Not sure what you mean by "full stop".

    To find NULL value, the criteria is "Is Null" or "Is Not Null".
    To find Blank value, the criteria is "".

    Not sure what you trying to accomplish. Using the "And" or "Or" to find data for "Null" value or "Blank" Value.
    Criteria: WHERE FINDDATA Is Null Or FINDDATA Like "*Something*"

    Hope this helps.
    Thanks for the reply.
    Full Stop = "." which can be found in some of the records in any position in the database field.
    It is these records I wish to ignore.
    I accept that I can find all the null and blank records using like Null and ""

    However there are additional records that contain data (alpha & Numeric).

    I want my query to return the NULL records, plus BLANK records, plus records with no full stop.

    Is this possible.

  4. #4
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    Try this. Not sure the full stop is just the . or "."

    If the full stop is just ., then

    SELECT *
    FROM TABLENAME
    WHERE TABLENAME.FINDDATA IS NULL OR TRIM(TABLENAME.FINDDATA) = "" OR NOT LIKE "."

    If the full stop is ".", then

    SELECT *
    FROM TABLENAME
    WHERE TABLENAME.FINDDATA IS NULL OR TRIM(TABLENAME.FINDDATA) = "" OR NOT LIKE '"."'

  5. #5
    pattrickcolin is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Location
    southend on sea
    Posts
    44
    Thank you for helping, but this doesn't work.

    TRIM doesn't seem to do anything.
    NOT LIKE '"."' removes any full stops (periods) records that only contain periods.I have no records matching this.

    I want my query to return NULL records + BLANK records + Any other DATA RECORD that doesn't contain a full stop(period) within the data.

    Any other suggestions?

  6. #6
    pattrickcolin is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Location
    southend on sea
    Posts
    44
    Quote Originally Posted by pattrickcolin View Post
    My Query field contains Nulls, Blanks, and data. This data may include a full stop (in any position).
    I need to extract all records that have no full stop.
    Using Not Like "*.* in the criteria, but it doesn't return blanks records.
    Tearing my hair out as I cannot seem to crack this.
    Any ideas.
    Making some Progress.
    A Section of the database contains 13,100 records
    4,470 are NULL, 158 are BLANK and 8,472 contains data =13,100

    When I do a Query for NOT LIKE "*.*" I get all the data without a full stop(period) plus the BLANKS =6603 records.
    When I do a Query for IS NULL I get all the NULL RECORDS =4470 records

    How do I Combine these statements to get the 11073 records that I want.

  7. #7
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    I would just change my criteria to include the "*" (wildcard) with the ".". Like this.

    WHERE (((Trim([TableData].[search data]))="")) OR (((TableData.[search data]) Is Null)) OR (((TableData.[search data]) Not Like "*.*"))

  8. #8
    pattrickcolin is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Mar 2014
    Location
    southend on sea
    Posts
    44
    Thankyou. You have solved my problem. It works 100%.

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

Similar Threads

  1. Replies: 4
    Last Post: 03-10-2014, 06:16 AM
  2. Replies: 1
    Last Post: 07-20-2012, 03:31 AM
  3. Replies: 3
    Last Post: 05-07-2012, 07:57 PM
  4. Extracting text from XML data
    By rob4465 in forum Access
    Replies: 1
    Last Post: 03-18-2010, 06:41 PM
  5. Extracting data after a keyword
    By KerryA in forum Queries
    Replies: 1
    Last Post: 02-11-2010, 09:56 PM

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