Empty date fields

  • Thread starter Thread starter Barbara Hall
  • Start date Start date
B

Barbara Hall

Because I am used to a different DBMS where handling
records where date fields are empty is a piece of cake,
Access is driving me crazy!

I have a database to record complaints from customers.
One of the fields is a date field to record the date that
the issue was resolved. I want a query to select records
where this field is empty to use in a report.

I've tried IsNull in the criteria field but I'm getting
Data Type Mismatch in Criteria Expression.

Please help!
 
Hi,


Probably a question of syntax.

In SQL, you can generally use

Expression Is Null

(Is Null in two words), like:


WHERE dateTimeField IS NULL


In VBA, you use

IsNull( Expression )

where IsNull is a single word. VBA functions can be used inside Jet if you
are using Access, so, in SQL with Jet and Access, you can use:


WHERE IsNull( Expression )

but that leads to a VBA call. Expression Is Null is preferred.



Hoping it may help,
Vanderghast, Access MVP
 
This works for me . .

SELECT test.nameg, test.date1
FROM test
WHERE (((test.date1) Is Null))

HTH

Al
 
Barbara

I can't tell, from your description, if the "date field" you mention is an
Access DateTime type field, or if you mean it is a field that holds a date.

Given the error message, my guess is the latter, and that the data type is
text.

More info, please...

Jeff Boyce
<Access MVP>
 
Thanks to everyone who has tried to email me some help,
unfortunately all emails to date have been blocked by
messagelabs virus checker. Anyone prepared to post some
ideas here?
 
Thanks to everyone who has tried to email me some help,
unfortunately all emails to date have been blocked by
messagelabs virus checker. Anyone prepared to post some
ideas here?
Click to expand...

Most of the volunteers prefer to post messages here rather than
emailing in any case. Sure...

Use

IS NULL

(two words). IsNull is a VBA function, not a criterion. It's confusing
because the SQL criterion and the function are almost alike in both
appearance and functionality - but they aren't the same! If you just
put IsNull on the criteria line, Access would convert it to a text
string - a type mismatch; if you put IsNull(), the function returns a
Boolean, not a date, again a type mismatch.
 
Back
Top