How to build a query for getting rows having a blank field in access whose datatype is date?

I am building a program in vb6 which asks me to get the rows in a table having return_date field which obviously has date/time datatype and it is blank.

So in my system I want to build a query to get the books not returned. i.e. return_date field is blank.

SELECT * 
FROM loans 
WHERE return_date = ""

I tried the above but it said datatype mismatch... error...!

Any suggestions...?开发者_JAVA百科

---

use single quotes, or if the field is null when there is no date then use where return_date is null

---

select * from loans where return_date IS NULL;

---

If return_date is indeed Date/Time data type, then blank means the field is Null. So use that as your WHERE condition.

SELECT * FROM Loans WHERE return_date Is Null;

Edit: The data type mismatch error happened because you were asking the database engine to compare a string value ("") with a Date/Time field.

Edit2: Another answer suggested single quotes. Single quotes will yield the same "Data type mismatch in criteria expression" error as the OP got using double quotes when comparing an empty string to a Date/Time field.

I created a loans table with id (autonumber) and return_date (Date/Time) fields, and added 2 records one with today's date for return_date, and the other with Null for return_date. This query throws the mismatch error ..

SELECT *
FROM loans
WHERE return_date='';

... just like this query ...

SELECT *
FROM loans
WHERE return_date="";

---

Maybe you should try

SELECT * 
FROM loans 
WHERE return_date Is Null