WHERE clause not finding Null Date field values. (1 Viewer)

[mvanella]

I have a table that I am trying to filter by a date field. I want to find all entries will null or blank values in the DateOut field. The table has a number of entries, about half have dates in the DateOut field, and half do not. I have tried:

WHERE DateOut Is Null;

WHERE IsNull(DateOut);

WHERE IsEmpty(DateOut);

WHERE DateOut = "";

None of them work. With the empty quotes, I get a data mismatch error. But with the others I just get an empty datasheet. If I check for not null values, it returns all of the entries that have dates in the field.

So these work:

WHERE DateOut Is Not Null;

WHERE Not IsNull(DateOut);

WHERE Not IsEmpty(DateOut);

but I actually am looking for the opposite.

 

[boblarson]

Is the field really a date/time data type or is it text?

 

[mvanella]

Yes it is a date/time field, not text.

 

[boblarson]

Yes it is a date/time field, not text.

Then this one SHOULD work:

WHERE DateOut Is Null;

Post the rest of the SQL for the query as it may have something to do with this.

 

[mvanella]

I fiddled around and you are correct. But the problem still persists because I am using a slightly different approach than I had previously specified, I thought this would be a simpler resolution, but it looks like there's more going on than I realized.

If I make my SQL SELECT statement based on the table itself, everything works great.

SELECT * FROM tblUnitInventory WHERE DateOut Is Null;

This works fine.

However, this table uses lookup values since it is a web table (my front-end application is all non-web and VBA compatible, though). So I created a query that joins all of the look up tables with the tblUnitInventory table so that it doesn't just display ID values in the lookup columns on a report. That query is named qryInventoryBase.

So my application where I am having the aforementioned problem is in referencing this query in another query.

My SQL looks like this:

SELECT * FROM qryInventoryBase WHERE DateOut Is Null;

And it is in this application that I don't have any results like I should.

qryInventoryBase looks like this:

SELECT tblUnitInventory.Serial, tblModel.Model, tblUnitInventory.Location, tblUnitInventory.PONum, tblInvenOwner.Owner, tblInvenStatus.Status, tblUnitInventory.Description, tblUnitInventory.InvNum, tblUnitInventory.RMA, tblType.Type, tblUnitInventory.DateIn, tblUnitInventory.DateOut, tblCustomer.Customer
FROM tblCustomer INNER JOIN (tblInvenOwner INNER JOIN (tblInvenStatus INNER JOIN (tblType INNER JOIN (tblModel INNER JOIN tblUnitInventory ON tblModel.ID = tblUnitInventory.Model) ON tblType.ID = tblModel.Type) ON tblInvenStatus.ID = tblUnitInventory.Status) ON tblInvenOwner.ID = tblUnitInventory.Owner) ON tblCustomer.ID = tblUnitInventory.SoldTo;

This query was constructed from the wizard where I dragged in all of the tables that link to the main table (Type links to Model, which links to UnitInventory).

I've tried modifying the syntax but to no avail as these do not work:

SELECT * FROM qryInventoryBase WHERE DateOut Is Null;

SELECT * FROM qryInventoryBase WHERE qryInventoryBase.DateOut Is Null;

SELECT * FROM qryInventoryBase WHERE qryInventoryBase.[DateOut] Is Null;

SELECT * FROM qryInventoryBase WHERE [DateOut] Is Null;

But the weird thing is that this works and returns all dates matching 1/1/2001

SELECT * FROM qryInventoryBase WHERE DateOut = #1/1/2001#;

 

[boblarson]

Your problem is the Inner joins in the qryInventoryBase. You need to use OUTER JOINS instead so that if there isn't a matching record to a table with the other table it will still return what is in the first table.

So your joins from your customer table should have the arrow pointing towards each other table. If you double-click on the line between the customer table and one of the others, it should bring up three options. The first, the Inner Join, will be the one selected. So you need to change to either the 2nd or 3rd option which will read something like this: Select all records from tblCustomers and only those records which match from TableNameHere.

 

[mvanella]

Your problem is the Inner joins in the qryInventoryBase. You need to use OUTER JOINS instead so that if there isn't a matching record to a table with the other table it will still return what is in the first table.

So your joins from your customer table should have the arrow pointing towards each other table. If you double-click on the line between the customer table and one of the others, it should bring up three options. The first, the Inner Join, will be the one selected. So you need to change to either the 2nd or 3rd option which will read something like this: Select all records from tblCustomers and only those records which match from TableNameHere.

That works perfectly. I tried the 2nd option but it said the joins were ambiguous and to make each join its own query. I wasn't about to make 5 queries, so I tried the 3rd option which made them all right joins. Then I saved it, and tried the SQL again and it works great. Thanks.