Problem with a query 1

[varnix]

I have a call log database that is currently populating quite well.

Later, after the db is updated another person is expected to review and close the call.

At day's end we need to see what calls are still open and what calls have been closed. When we do the initial entry we are not inputting any data into the closedate field.

Therefore when we go to run our reports at day's end we're running this query:


<cfquery name=&quot;getlogs&quot; datasource=&quot;#application.DS#&quot;>
select *
from calllog
where group = '#url.group#'
<cfif url.action is &quot;closed&quot;>
and closedate <> ''
<cfelseif url.action is &quot;open&quot;>
and closedate = ''
</cfif>
</cfquery>


We get all of the closed logs just fine, but none of the open logs are coming through. We've tried change it from closedate = '' to = NULL, 'NULL', ' ' - we're probably just missing something obvious, but we can't see it.

HELP!

Thanks! =)

Roger

 

[CFDude]

Have you tried

and closedate IS NULL

OR

and closedate IS NOT NULL

No quotes or anything around it. Your date is a &quot;date&quot; type in the database, right? If you still have a problem, let us know what DB you are using too.

HTH,
Tim P.

 

[GunJack]

Hey Roger,

I believe Tim's suggestions above work on sql server but for an access db, you'll need to use this syntax:

isnull(closedate)
not isnull(closedate)

Good luck,
GJ

 

[varnix]

THanks!

The is null worked perfectly.

I had tried = null thinking incorrectly that I couldn't use is in the query.


Thanks again!

Roger

Yesterday was a very long week.
Today is going to be an even longer one.