IsNull not working in query (1 Viewer)

[hrdpgajjar]

Hi all,
I have a table named "Master Data" having following columns

- Regi No
- Farmer Name
- MIS System
- Material Supply Date
- DD Or RTGS No
- Material Supply Date
- TPA Date


I am trying to run a query where,

- Material Supplied (Not Null)
-DD Or RTGS No Pending (Is Null)
- TPA Date pending (Is null)

It works properly but as is select "DD Or Rtgs No" as "Is Null"

it returns blank report.

Earlier this query works fine but suddenly stops working

Below is my SQL View

SELECT [Master Data].[DD Or Receipt No], [Master Data].[Material Supply Date], [Master Data].[WO Date], [Master Data].[TPA Date], [Master Data].[Regi No], [Master Data].[Farmer Name], [Master Data].[MIS System]
FROM [Master Data]
WHERE ((([Master Data].[DD Or Receipt No]) Is Null) AND (Not ([Master Data].[Material Supply Date]) Is Null) AND (Not ([Master Data].[WO Date]) Is Null) AND (([Master Data].[TPA Date]) Is Null));


There are some records having above criteria but it shows blank


help appreciated

Thanks

 

[MajP]

DD Or RTGS No

You are begging for problems with name schema like that. Never put spaces in a name, but for sure never put a space in a name with something like "And " or "Or"

DD_or_RTGS_NO is fine.

Readable.

SELECT [master data].[dd or receipt no],
       [master data].[material supply date],
       [master data].[wo date],
       [master data].[tpa date],
       [master data].[regi no],
       [master data].[farmer name],
       [master data].[mis system]
FROM   [master data]
WHERE  ( ( ( [master data].[dd or receipt no] ) IS NULL )
         AND ( NOT ( [master data].[material supply date] ) IS NULL )
         AND ( NOT ( [master data].[wo date] ) IS NULL )
         AND ( ( [master data].[tpa date] ) IS NULL ) );

try

WHERE  ([master data].[dd or receipt no] ) is null AND [master data].[tpa date] IS NULL)
         AND NOT ( [master data].[material supply date] ) IS NULL AND  [master data].[wo date] IS NULL )

 

[DHookom]

Are you positive your fields are not zero-length-strings rather than Null?
I have always used
FieldName Is Not Null
rather than
Not FieldName Is Null

 

[hrdpgajjar]

Are you positive your fields are not zero-length-strings rather than Null?
I have always used
FieldName Is Not Null
rather than
Not FieldName Is Null

SELECT [Master Data].[Regi No], [Master Data].DD_ReceiptNo, [Master Data].[WO Date], [Master Data].[TPA Date], [Master Data].[Farmer Share Deposite Date]
FROM [Master Data]
WHERE ((([Master Data].DD_ReceiptNo) Is Not Null));


Still it returns both fields having value or not having value (null)



what i am doing wrong?

 

[The_Doc_Man]

In theory, that is a valid way to ask for only the non-null fields, but try NOT ISNULL(DD_ReceiptNo) rather than DD_ReceiptNo IS NOT NULL. The IS NULL and IS NOT NULL are special comparison syntax cases, but ISNULL(x) is a function that does its test differently. If both methods (comparison or function test) fail to find a NULL then you don't have one. Whatever you see is something else.

By the way, since that is a single-table query, you can omit [Master Date] everywhere you used it EXCEPT in the FROM clause, where you DO have to use the table name. You only have to qualify field names if there is a choice, and for single-table queries, there IS no choice.

 

[hrdpgajjar]

In theory, that is a valid way to ask for only the non-null fields, but try NOT ISNULL(DD_ReceiptNo) rather than DD_ReceiptNo IS NOT NULL. The IS NULL and IS NOT NULL are special comparison syntax cases, but ISNULL(x) is a function that does its test differently. If both methods (comparison or function test) fail to find a NULL then you don't have one. Whatever you see is something else.

By the way, since that is a single-table query, you can omit [Master Date] everywhere you used it EXCEPT in the FROM clause, where you DO have to use the table name. You only have to qualify field names if there is a choice, and for single-table queries, there IS no choice.

SELECT [Regi No],[DD_ReceiptNo],[WO Date]

FROM [Master Data]

WHERE NOT ISNULL ([DD_ReceiptNo])

This still returns both null and not null values in (DD_ReceiptNo)....

 

[Josef P.]

Null or not null, that is the question.

SELECT [Regi No],[DD_ReceiptNo],[WO Date]
FROM [Master Data]
WHERE [DD_ReceiptNo] IS NULL

SELECT [Regi No],[DD_ReceiptNo],[WO Date]
FROM [Master Data]
WHERE [DD_ReceiptNo] IS NOT NULL

If varchar:
SELECT [Regi No],[DD_ReceiptNo],[WO Date]
FROM [Master Data]
WHERE [DD_ReceiptNo] = ''

SELECT [Regi No],[DD_ReceiptNo],[WO Date]
FROM [Master Data]
WHERE [DD_ReceiptNo] > ''

 

[hrdpgajjar]

Null or not null, that is the question.

SELECT [Regi No],[DD_ReceiptNo],[WO Date]
FROM [Master Data]
WHERE [DD_ReceiptNo] IS NULL

SELECT [Regi No],[DD_ReceiptNo],[WO Date]
FROM [Master Data]
WHERE [DD_ReceiptNo] IS NOT NULL

If varchar:
SELECT [Regi No],[DD_ReceiptNo],[WO Date]
FROM [Master Data]
WHERE [DD_ReceiptNo] = ''

SELECT [Regi No],[DD_ReceiptNo],[WO Date]
FROM [Master Data]
WHERE [DD_ReceiptNo] > ''

Now the bigger question for newbee like me is, How to handle null values in a proper way in a data base ??? what is the best way to sort "Null" And "Not null" values in a large database?

 

[hrdpgajjar]

You are begging for problems with name schema like that. Never put spaces in a name, but for sure never put a space in a name with something like "And " or "Or"

DD_or_RTGS_NO is fine.

Readable.

SELECT [master data].[dd or receipt no],
       [master data].[material supply date],
       [master data].[wo date],
       [master data].[tpa date],
       [master data].[regi no],
       [master data].[farmer name],
       [master data].[mis system]
FROM   [master data]
WHERE  ( ( ( [master data].[dd or receipt no] ) IS NULL )
         AND ( NOT ( [master data].[material supply date] ) IS NULL )
         AND ( NOT ( [master data].[wo date] ) IS NULL )
         AND ( ( [master data].[tpa date] ) IS NULL ) );

try

WHERE  ([master data].[dd or receipt no] ) is null AND [master data].[tpa date] IS NULL)
         AND NOT ( [master data].[material supply date] ) IS NULL AND  [master data].[wo date] IS NULL )

Still not getting the result i want. Is there problem in count null or not null values in "Short Text" type ?

 

[CJ_London]

Proper way depends on what your app is required to do. But a good starting point is to not allow ZLS's (Zero Length String) to be consistent in treatment of values regardless of whether they are text or number.

In your table design, field properties, set 'allow zero length' to no - any zls's (zero length string) will be converted to null. And whilst you are at it remove default values from any FK's (Foreign Keys) so they are also Null.

Also remove any formatting in your field properties as formatting hides the underlying data

in vba or sql code to convert a null to a value, use the nz function - these are equivalent

in sql: value is null or value is not null
in vba: isnull(value) or not isnull(value)

in both the equivalent nz function would be nz(value,"")="" or nz(value,0)=0 (depending on whether value could be a string or number)

If you don't want, or are unable to, not alllow ZLS's, use the nz function

As an aside, one of the benefits of not allowing ZLS's is you can put a prompt text in the control (not the field) by setting the format property to say

"Enter Name Here";@

or

"Not Required";@

Format properties have two parts for for formatting, separated by a semi colon. The first part is for null and the second to display the value

For numbers there are 4 parts
positive - e.g. #,##0.00
negative - e.g. [red](#,##0.00)
zero - e.g. 0.00
null - e.g. -

But digressing

 

[hrdpgajjar]

You are begging for problems with name schema like that. Never put spaces in a name, but for sure never put a space in a name with something like "And " or "Or"

DD_or_RTGS_NO is fine.

Readable.

SELECT [master data].[dd or receipt no],
       [master data].[material supply date],
       [master data].[wo date],
       [master data].[tpa date],
       [master data].[regi no],
       [master data].[farmer name],
       [master data].[mis system]
FROM   [master data]
WHERE  ( ( ( [master data].[dd or receipt no] ) IS NULL )
         AND ( NOT ( [master data].[material supply date] ) IS NULL )
         AND ( NOT ( [master data].[wo date] ) IS NULL )
         AND ( ( [master data].[tpa date] ) IS NULL ) );

try

WHERE  ([master data].[dd or receipt no] ) is null AND [master data].[tpa date] IS NULL)
         AND NOT ( [master data].[material supply date] ) IS NULL AND  [master data].[wo date] IS NULL )

Attached my Database herewith, please create a query with the following criteria,
1. Material Supply Date - Not null
2. TPA Date - Is Null
3. DD_ReceiptNo - Is Null


help appreciated


Thanks

 

[NauticalGent]

- any zls's (zero length string) will be converted to null.

Was not aware of that behavior. Makes sense when you think about it.

 

[arnelgp]

I am trying to run a query where,

- Material Supplied (Not Null)
-DD Or RTGS No Pending (Is Null)
- TPA Date pending (Is null)

How about (Query1):

SELECT [Master Data].*
FROM [Master Data]
WHERE
    (NOT ([MATERIAL SUPPLY DATE]  IS NULL)) AND 
    ([TPA DATE]  IS NULL)  AND
    ([DD_RECEIPTNO] IS NULL);