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

[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

 

[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

 

[Dirk Goldgar]

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

 

[RoyVidar]

select count(STARTDATE) from PEOPLE where STARTDATE is null

Try

select count(*) from PEOPLE where STARTDATE is null

 

[Clif McIrvin]

Hi All

select count(STARTDATE) from PEOPLE where STARTDATE is null

Maybe:
select count(nz(STARTDATE,0)) from PEOPLE where STARTDATE is null


???

 

[Laphan]

Many thanks everybody this did the trick!!

Hi All

select count(STARTDATE) from PEOPLE where STARTDATE is null

Maybe:
select count(nz(STARTDATE,0)) from PEOPLE where STARTDATE is null


???

 

[Dirk Goldgar]

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.