SQL - Invalid use of IS operator in query expression

[silvionr]

Can anyone tell what is wrong with this select statement?

Select Statement:
***************************************************

SELECT [Primary Number] FROM [Vital Stats] WHERE [Title-D] IS NULL OR [First Name-D] IS NULL OR
[Last Name-D] IS NULL OR [Inform Title] IS NULL OR [Inform first Name] IS NULL OR
[Inform Last Name] IS NULL OR [Address-infor-1] IS NULL OR [City/town-infor] IS NULL OR
[State-infor] IS NULL OR [ZipCode-infor] IS NULL OR [Inf RevRel] IS NULL OR [Director Name] IS NULL IN(selectedCases)

***************************************************

I'm using VB .NET and the data is being pulled (or should be pulled I should say) from an ACCESS Database

I keep getting this error message:

"Invalid use of IS operator in query expression [Title-D] IS NULL OR [First Name-D] IS NULL OR...."

I want to pull the records that contain a NULL value in any of those fields listed in the select statement.

Any suggestion is valid, since I can't get it to work.

Thank you,


silvionr

 

[Remou]

When I paste the above into the Access query design screen, skippin the last bit:

<...>IN(selectedCases)

Everything is fine. With the last bit, I get "Invalid use of Is operator in criteria expression" and the SQL is truncated.

 

[silvionr]

Thanks for the quick reply,

You’re right, once “IN(selectedCases)” is removed, it works, but not ecxatly the way it should.

The string “selectedCases” contains the primary keys of the records I want to check for NULL Values, but aparently that’s the wrong way of doing it.

In other words, I have a list of primaryKeys (selectedCases), and I want to pull the records that contain NULL values, but only from the records whose primary (selectedCases) I’m supplying, not from the entire database.

Just as an example, the following code works perfectly:

SELECT [Primary Number], [Title-D] FROM [Vital Stats]
WHERE [Primary Number] IN(selectedCases)

Any other suggestion?

Thanks again for your quick reply,

silvionr

 

[Remou]

At a guess I'd say you are missing a couple of words. Try:

[tt]SELECT [Primary Number] FROM [Vital Stats] WHERE [Primary Number] IN (selectedCases) AND ([Title-D] IS NULL OR [First Name-D] IS NULL OR
[Last Name-D] IS NULL OR [Inform Title] IS NULL OR [Inform first Name] IS NULL OR
[Inform Last Name] IS NULL OR [Address-infor-1] IS NULL OR [City/town-infor] IS NULL OR
[State-infor] IS NULL OR [ZipCode-infor] IS NULL OR [Inf RevRel] IS NULL OR [Director Name] IS NULL)[/tt]