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

[pattrickcolin]

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.

[lfpm062010]

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.

[pattrickcolin]

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.

[lfpm062010]

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 '"."'

[pattrickcolin]

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?

[pattrickcolin]

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.

[lfpm062010]

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 "*.*"))

[pattrickcolin]

Thankyou. You have solved my problem. It works 100%.