Is Not Null

[karenmierkalns]

I am having a problem getting the exact syntax for using not null in the where clause.

For example,

select * from table where field is not null;

Is this correct, or is it simply 'not null'? I'm using Visual Basic to access my Access database.

I haven't been able to find the opposite of IS NULL.

Thanks, - Karen

 

[Jerrycurl]

IS NOT NULL should work, although I don't use Access enough to know if it has some sort of peculiarity about this. Are you getting an error or just not getting the rows you expect?

 

[karenmierkalns]

I was getting an error. I was getting a different kind of error than usual when I used Is Not Null, and a usual error (syntax error) with plain old Not Null.

However, I retyped in Is Not Null, and magically it is working. I suppose it could have been a typo.

Thanks for the quick reply! - Karen

 

[link9]

IS NOT NULL only works in SQL Server (or at least I can say it doesn't work in Access)

select * from table where not isNull(field)

is the syntax you are after

 

[tlbroadbent]

Is Not Null certainly works in Access queries. Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[karenmierkalns]

The error message I am getting is:

Data provider or other service returned an E-FAIL status.

This happened with
where field Is Not Null

and

where not isnull(field)

I am under the impression that these are valid syntaxes, but do not work with VB/Access?

Any other suggestions?

Thanks. - Karen

 

[karenmierkalns]

I think it may be my sql statement. I tried the same syntax on a two table join, and it worked fine.

Also, it may not be necessary, because I am redesigning my database so as to eliminate the need to even do the query with where not isnull(field).

Thanks anyway. - Karen