Solved Query with multiple criteria

[hrdpgajjar]

Hi all,
I am trying to generate data with following criteria in my database,

1. File sent column = is null
2. Status column = Not equals to "return" or Is null
3. Physical file column = Not equals "IN QUERY"

here is my query in SQL VIEW,
SELECT [App Data].[Regi No], [App Data].[Farmer Name], [App Data].Area, [App Data].[Sent To BRD], [App Data].Status, [App Data].[Physical File At] FROM [App Data] WHERE ((([App Data].[Sent To BRD]) Is Null) AND (([App Data].Status)<>"Return to field" Or ([App Data].Status) Is Null) AND (([App Data].[Physical File At])<>"IN QUERY"));

it shows zero in result,

If i make change as physical file column = "IN QUERY" then it shows only files in query


What i want is to fulfill all three criteria's. I need list of cases where,

1. File sent column = is null
2. Status column = Not equals to "return" or Is null
3. Physical file column = Not equals "IN QUERY"


pls help

Thank You,

 

[CJ_London]

Have you tried using the query builder - looks like your brackets may be incorrectly paired.

or remove all the brackets and just replace them around the second requirement

 

[hrdpgajjar]

Have you tried using the query builder - looks like your brackets may be incorrectly paired.

or remove all the brackets and just replace them around the second requirement

This is the result from Query builder itself. I have not tried to change anything.

 

[CJ_London]

In that case, implies you don’t have any data to fit the criteria

 

[CJ_London]

if you remove all the brackets and replace just around criteria 2 (the OR) I would expect it to look like this

SELECT [App Data].[Regi No], [App Data].[Farmer Name], [App Data].Area, [App Data].[Sent To BRD], [App Data].Status, [App Data].[Physical File At]
FROM [App Data]
WHERE [App Data].[Sent To BRD] Is Null AND ([App Data].Status<>"Return to field" Or [App Data].Status Is Null) AND [App Data].[Physical File At]<>"IN QUERY"

Generally speaking, bad idea to have spaces in table and field names - makes the code harder to read and forces the requirement to use square brackets

 

[Rettogo]

Hi all,
I am trying to generate data with following criteria in my database,

1. File sent column = is null
2. Status column = Not equals to "return" or Is null
3. Physical file column = Not equals "IN QUERY"

here is my query in SQL VIEW,

SELECT [App Data].[Regi No], [App Data].[Farmer Name], [App Data].Area, [App Data].[Sent To BRD], [App Data].Status, [App Data].[Physical File At]
FROM [App Data]
WHERE ((([App Data].[Sent To BRD]) Is Null) AND (([App Data].Status)<>"Return to field" Or ([App Data].Status) Is Null) AND (([App Data].[Physical File At])<>"IN QUERY"));

it shows zero in result,

If i make change as physical file column = "IN QUERY" then it shows only files in query


What i want is to fulfill all three criteria's. I need list of cases where,

1. File sent column = is null
2. Status column = Not equals to "return" or Is null
3. Physical file column = Not equals "IN QUERY"


pls help

Thank You,

Access has a bad habit of loading a query with FAR too much unnecessary overhead. I copied your query into Notepad an removed the table name from the field list (since you are querying from a single table) along with any unnecessary parentheses. I suggest not using spaces in the names of tables, fields, etc.; use underscore _ instead. When you don't have spaces or other odd characters in names, you won't need all of the square brackets which make queries harder to read. Here's what I got when I removed what I could:

SELECT [Regi No], [Farmer Name], Area, [Sent To BRD], Status, [Physical File At]
FROM [App Data]
WHERE [Sent To BRD] Is Null AND (Status <> "Return to field" Or Status Is Null) AND [Physical File At] <> "IN QUERY";

Now, based on what is left, I am reading that you want to select records where all three conditions are met:
1. Sent To BRD is a null
2. Status is null or anything other than "Return to field"
3. Physical File At is anything other than "IN QUERY"
May I offer two suggestions that will help you in writing queries? First, put all of your AND conditions up front in the where clause. If one clause comes back false, we need look no further to know that no data will be selected from that record. Second, I understand that you want to get records where the Status is anything other than "Return to field" INCLUDING where the status is null. When you have a condition like this, put the fact that you want NULL values first in the OR clause. When I make these changes, I get the following:

SELECT [Regi No], [Farmer Name], Area, [Sent To BRD], Status, [Physical File At]
FROM [App Data]
WHERE [Sent To BRD] Is Null AND [Physical File At] <> "IN QUERY" AND (Status Is Null OR Status <> "Return to field");

This rearrangement of the Status is null to the first condition in the OR clause would likely resolve your issue. You could copy the text above, go to SQL View of your query and replace what is there with this text using Paste and this should work.

 

[Pat Hartman]

The problem is with this part of the expression:

([App Data].[Physical File At] <> "IN QUERY")

Why? The default for [Physical File At] is probably Null and NOTHING ever equals Null.

There may be a different way to build the expression depending on what the other values of the field are. Or, you can do this:

([App Data].[Physical File At] & "" <> "IN QUERY") --- This essentially converts the field value to ZLS rather than null and you can get a true return when you compare ZLS to a field.

Null <> "something" always equals null which is different from true and that is why you get no rows returned.