Null Date in select statement

Not sure how to do this but what Ive tried hasent worked.

SQL = "SELECT ID,FirstName,LastName FROM MySchedule WHERE MyDate IS NOT
NULL"

Need to get all records that have a date in the MyDate field.

What's wrong with this?

Thanks!

Re: Null Date in select statement

BTW - Im using an Access database and the field type is shortdate if that
makes a difference.

Thanks in advance.

"Jake" wrote in message
news:%23e7ssb9EFHA.1836@tk2msftngp13.phx.gbl...
> Not sure how to do this but what Ive tried hasent worked.
>
> SQL = "SELECT ID,FirstName,LastName FROM MySchedule WHERE MyDate IS NOT
> NULL"
>
> Need to get all records that have a date in the MyDate field.
>
> What's wrong with this?
>
> Thanks!
>

Re: Null Date in select statement

Jake wrote on 16 feb 2005 in microsoft.public.inetserver.asp.db:

> SQL = "SELECT ID,FirstName,LastName FROM MySchedule WHERE MyDate IS NOT
> NULL"
>

... WHERE NOT isNull(MyDate)"



--
Evertjan.
The Netherlands.
(Replace all crosses with dots in my emailaddress)

Re: Null Date in select statement

Jake wrote on 16 feb 2005 in microsoft.public.inetserver.asp.db:
> BTW - Im using an Access database and the field type is shortdate if
> that makes a difference.

"shortdate" defaults to "date" in a Jet + ADODB connection.

I think!

--
Evertjan.
The Netherlands.
(Replace all crosses with dots in my emailaddress)

Re: Null Date in select statement

Jake wrote:
> BTW - Im using an Access database and the field type is shortdate if
> that makes a difference.
>
> Thanks in advance.
>
> "Jake" wrote in message
> news:%23e7ssb9EFHA.1836@tk2msftngp13.phx.gbl...
>> Not sure how to do this but what Ive tried hasent worked.
>>
>> SQL = "SELECT ID,FirstName,LastName FROM MySchedule WHERE MyDate IS
>> NOT NULL"
>>
>> Need to get all records that have a date in the MyDate field.
>>
>> What's wrong with this?
>>
>> Thanks!

If MyDate is a Date/Time field (the format: is irrelevant: it has nothing to
do with the way the values are stored) then

where mydate is not null
or
where not mydate is null

should work. If MyDate is a Text field, containing empty strings ("")
instead of Nulls, then this statement will return the empty strings (an
empty string is not Null).

Here is a variation that will work, assuming it's a Date/Time field and you
know the earliest date the field is likely to hold (let's assume 1950-01-01
for the sake of this example):

where mydate > #1950-01-01#

If it's a Text field, then you are going to have to do something like

where mydate > "0"

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Null Date in select statement

What type of field - Text or DateTime - exactly is the field MyDate? Looks
like that it is a text field instead of a Date/Time.

S. L.

"Jake" wrote in message
news:%23e7ssb9EFHA.1836@tk2msftngp13.phx.gbl...
> Not sure how to do this but what Ive tried hasent worked.
>
> SQL = "SELECT ID,FirstName,LastName FROM MySchedule WHERE MyDate IS NOT
> NULL"
>
> Need to get all records that have a date in the MyDate field.
>
> What's wrong with this?
>
> Thanks!
>