Null Function

[pgh2377]

I'm trying to build a query the pulls the null date. A patient may have several dates being serviced but while he is currently being serviced the date field will be blank. How could I write a null function telling the query only to pull the blank field (End date)

Currently I have Nz([End_date]) but it doesn't work

 

[jrbarnett]

The Nz function returns null only if the value in the field is null. If you want to retrieve a list of values where the date field is null, in the criteria window in the query design, put:

Is Null

This translates to the following SQL query code:

select field1, field2
from table
where field3 is null

John

 

[pgh2377]

I tried that unfortunately it pulls only the null values. Unfortunately if the patient is non serviceable they will have an end dat so I need to pull both non serviceable and the one that currenlt are being service which will have the null value. Any suggestions or should I just write several queries?

 

[jrbarnett]

You can union it to the query that will pull out those with values in, thus:

select field1, field2
from table
where field3 is null
union
select field1, field2
from table
where field3 is not null

Obviously this query is rather pointless, because it would select everything, but I'm sure you get the idea.

John