Cannot extrapolate NULL Values

[Jedi420]

I have a project database that managers used to keep track of their projects. One aspect of each projects record is the resource (people) who works on the project. I'm trying to pull up a list of all the projects that do not have anything listed in their 'Resource' field. The table is called [Project Information].
My problem is that my DCount() function returns 0 (zero) when it's called this way.

Dim crit as String

crit = "[Resource] is NULL"
     
For i = 1 To DCount("Resource", "[Project Information]", crit)
     
     'Do stuff

Next i

That cannot be ... since this statement returns the correct number of projects (total projects - Null Resource Projects):

Dim crit as String

crit = &quot;[Resource] <> NULL&quot;
     
For i = 1 To DCount(&quot;Resource&quot;, &quot;[Project Information]&quot;, crit)
     
     'Do stuff

Next i

The 'Resource' field is text and I've tried almost everthing to get me the records that have no Resources listed. I've tried setting crit to:

crit = "[Resource] = ''"    OR
crit = "[Resource] = NULL"

... but nothing works, I keep getting a value of zero returned. This is very frustrating seeing as how I can pull out all the non-NULL values, but not the null ones. Maybe I'm missing something. Any help or suggestions anyone could offer would be greatly appreciated. (^_^). Thnx!

-Jedi420


A man who has risked his life knows that careers are worthless, and a man who will not risk his career has a worthless life.

 

[WildHare]

I'm assuming that RESOURCE is a field in the Project Information table.

If you think about it, it could have three possible values:

1) Null
2) Valid Text
3) Valid, but INVISIBLE.. e.g. , spaces.

Do a query on this table, but select for RESOURCE values that are NOT NULL ( "is not null&quot in the criteria cell.

Sort the results of this query on the RESOURCE field, in ASCENDING order.

The guys with a space or two will be at the top.

This sounds like it might be what your problem is.

HOWEVER,

If your "Resource" field is in another table that you have linked to the Project info table, you will want to use an OUTER JOIN to get them..

Details forthcoming if this is the case.. I don't need to bore you if the former solution does the trick...

JMH

Don't be sexist - Broads hate that!

Another free Access forum:

http://www.forumco.com/jmhare

More Access help stuff at

http://www.jmhare.com/wildhare.htm

 

[JonFer]

Instead of using DCount, create a recordset using the SQL:

"Select * from [Project Information] where Resource Is Null"

Then loop through each record with .MoveNext and do whatever you need to do with each record.

 

[dhookom]

Try:

Dim crit as String
crit = "IsNull([Resource])"
For i = 1 To DCount("Resource", "[Project Information]", crit)


Duane
MS Access MVP
Find out how to get great answers faq219-2884.