Really simple date query won't work ... aaaarrrrgghh


L

Laphan

Hi All

I must be doing something realy thick here!

I have a bog standard table that looks basically like the following:

ID AutoNum
Name Text
StartDate DateTime
DOB DateTime
Status Integer

User inputs data into this table data, but the start date and dob are not
mandatory fields.

I wanted to do a simple kind of checklist report that explains to them how
many entries don't have a name, startdate, etc and the text and integer
based queries were 2 second jobs however the DateTime ones won't work.

My query is simply:

select count(STARTDATE) from PEOPLE where STARTDATE= ''

The above generates a date type error so I changed it to:

select count(STARTDATE) from PEOPLE where STARTDATE is null

but it brings back a result of zero even though I know 2 entries don't have
a date. If I do the query the other way, ie not null, then I get the number
of people less the 'no start date' people, so the query knows that these 2
don't have a date but the count won't return it.

Am I right in thinking that I have to reverse query, ie get the total
people, get the total of people who have a start date and then subtract one
from another to get my result???

Pants!!

Rgds

Laphan
 
Ad

Advertisements

G

George

Null and the Zero Length String (ZLS) or '' are NOT the same thing.

If you have a ZLS in a field, that means it is not null, so your query won't
find it if NULL is the criteria used in the query.

Okay, so to search for Date fields where there are Nulls as well as ZLS's,
then I would use this following criteria.

Select Count(STARTDATE) from PEOPLE where Len(STARTDATE & "") = 0
 
D

Dirk Goldgar

Laphan said:
Hi All

I must be doing something realy thick here!

I have a bog standard table that looks basically like the following:

ID AutoNum
Name Text
StartDate DateTime
DOB DateTime
Status Integer

User inputs data into this table data, but the start date and dob are not
mandatory fields.

I wanted to do a simple kind of checklist report that explains to them how
many entries don't have a name, startdate, etc and the text and integer
based queries were 2 second jobs however the DateTime ones won't work.

My query is simply:

select count(STARTDATE) from PEOPLE where STARTDATE= ''

The above generates a date type error so I changed it to:

select count(STARTDATE) from PEOPLE where STARTDATE is null

but it brings back a result of zero even though I know 2 entries don't
have
a date. If I do the query the other way, ie not null, then I get the
number
of people less the 'no start date' people, so the query knows that these 2
don't have a date but the count won't return it.

Am I right in thinking that I have to reverse query, ie get the total
people, get the total of people who have a start date and then subtract
one
from another to get my result???


When the Count() aggregate function is applied to a specific field, it only
counts those records where the field is not Null. That's why ...
select count(STARTDATE) from PEOPLE where STARTDATE is null

.... is coming up as zero. But you can tell the Count() function to count
records, not fields, by saying Count(*). Try this:

select count(*) from PEOPLE where STARTDATE is null
 
Ad

Advertisements

D

Dirk Goldgar

George said:
Null and the Zero Length String (ZLS) or '' are NOT the same thing.

If you have a ZLS in a field, that means it is not null, so your query
won't find it if NULL is the criteria used in the query.

Okay, so to search for Date fields where there are Nulls as well as ZLS's,
then I would use this following criteria.

Select Count(STARTDATE) from PEOPLE where Len(STARTDATE & "") = 0


Ah, but if StartDate is a DateTime field, as we have been told, it cannot
contain a zero-length string.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top