Not Null Condition

[FrancisC]

I am using ADO to access an access database. I am trying to do a count of records where a value is not null. What is the exact sytax for that ?

What I have is :

"select count(*) from Table where Field1='Some Value' and Field2 is not null"


Field1 is a string and Field2 is a long integer.

Any ideas ?... what am I doing wrong ?

[JHausmann]

Nothing wrong with your SQl. Are you sure there are actual null values in your database for field2?

[davidrobin]

Try this query

"SELECT COUNT(*) FROM Table WHERE Field1='Some Value' and NOT Field2 IS NULL"

It works for SQL server

Remember to watch the Field1 value for containing the ' character, it will let the query up.

[kovan]

hmm never seen (*)
i guess that works since 2 people says
here is how i would do a record count

SELECT COUNT(PrimaryKey)as Total FROM Table WHERE Field1='SomeValue' AND Field2 IS NOT NULL

then loop throu the record set, after done
msgbox "There are " & RS!Total

hope that helps

[davidrobin]

I think Kovan may have pointed out a floor in the statement. Make sure you have the AS <name> after the COUNT(*) as in the following.

"SELECT COUNT(*) as Total FROM Table WHERE Field1='Some Value' and NOT Field2 IS NULL"

If not how can you reference the contents of the recordset.

[FrancisC]

Thank you all for your help...

I think I need to thank Microsoft for that one.... NOT !!

My problem was that the field in my condition was '1310Green'. For some reason, I can use it in the 'Select' part of the SQL, but not in the 'Where'.

I changed to 'Green1310' and it works perfectly...

Thanks again !