Filter Recordset against Null? 
Author Message
 Filter Recordset against Null?

How the heck do I filter a recordset (already constructed) to select (or
exclude) records for which a particular field is Null? The obvious filter
string, "foo Is Null" fails with an error and checking the docs on ADO it
appears you cannot specify Null as a value in a filter string!!

So, is it simply not possible to do this sort of filter in ADO?

Thanks in advance,
 Bill



Sun, 08 May 2005 23:42:25 GMT  
 Filter Recordset against Null?
Bill,

It is undocumented but it works

.Filter="FieldName=NULL"

--
Val Mazur
Microsoft MVP


Quote:
> How the heck do I filter a recordset (already constructed) to select (or
> exclude) records for which a particular field is Null? The obvious filter
> string, "foo Is Null" fails with an error and checking the docs on ADO it
> appears you cannot specify Null as a value in a filter string!!

> So, is it simply not possible to do this sort of filter in ADO?

> Thanks in advance,
>  Bill



Mon, 09 May 2005 00:03:02 GMT  
 Filter Recordset against Null?
Val-
  Thanks, that does indeed work. It is not only undocumented, but
contradicts the documentation!

Bill


Quote:
> Bill,

> It is undocumented but it works

> .Filter="FieldName=NULL"

> --
> Val Mazur
> Microsoft MVP



> > How the heck do I filter a recordset (already constructed) to select (or
> > exclude) records for which a particular field is Null? The obvious
filter
> > string, "foo Is Null" fails with an error and checking the docs on ADO
it
> > appears you cannot specify Null as a value in a filter string!!

> > So, is it simply not possible to do this sort of filter in ADO?

> > Thanks in advance,
> >  Bill



Mon, 09 May 2005 12:13:23 GMT  
 Filter Recordset against Null?
Bil,

Are you filtering in recordset or you need to filter in WHERE clause? In
case of Filter = Null should work and as I know works fine in several
applications. Actually documentation for ADO does not say anything about it.
You probably mix it with documentation for Transact-SQL. What is your code?
Which version of ADO are you using?

--
Val Mazur
Microsoft MVP


Quote:
> Val-
>   Thanks, that does indeed work. It is not only undocumented, but
> contradicts the documentation!

> Bill



> > Bill,

> > It is undocumented but it works

> > .Filter="FieldName=NULL"

> > --
> > Val Mazur
> > Microsoft MVP



> > > How the heck do I filter a recordset (already constructed) to select
(or
> > > exclude) records for which a particular field is Null? The obvious
> filter
> > > string, "foo Is Null" fails with an error and checking the docs on ADO
> it
> > > appears you cannot specify Null as a value in a filter string!!

> > > So, is it simply not possible to do this sort of filter in ADO?

> > > Thanks in advance,
> > >  Bill



Mon, 09 May 2005 20:53:07 GMT  
 Filter Recordset against Null?
Val-
  BTW, the documentation I quoted from can be found at
ado210.chm::/htm/mdproFilter.htm, assuming you have this help file
installed. Finally, I'm using ADO 2.6 and VB6SP4.

Thanks for the help,
Bill


Quote:
> Bil,

> Are you filtering in recordset or you need to filter in WHERE clause? In
> case of Filter = Null should work and as I know works fine in several
> applications. Actually documentation for ADO does not say anything about
it.
> You probably mix it with documentation for Transact-SQL. What is your
code?
> Which version of ADO are you using?



Fri, 13 May 2005 21:33:02 GMT  
 Filter Recordset against Null?
Val-
  This is an Access app, not SQL Server. Actually documentation for ADO
*DOES* say something about it.  In particular, (from the ADO Programmer's
Reference, online help), it says:

Value is the value with which you will compare the field values (for
example, 'Smith', #8/24/95#, 12.345 or $50.00). Use single quotes with
strings and pound signs (#) with dates. For numbers, you can use decimal
points, dollar signs, and scientific notation. If Operator is LIKE, Value
can use wildcards. Only the asterisk (*) and percent sign (%) wild cards are
allowed, and they must be the last character in the string. Value cannot be
Null.

Note the last sentence in the above description.

Bill


Quote:
> Bil,

> Are you filtering in recordset or you need to filter in WHERE clause? In
> case of Filter = Null should work and as I know works fine in several
> applications. Actually documentation for ADO does not say anything about
it.
> You probably mix it with documentation for Transact-SQL. What is your
code?
> Which version of ADO are you using?

> --
> Val Mazur
> Microsoft MVP



> > Val-
> >   Thanks, that does indeed work. It is not only undocumented, but
> > contradicts the documentation!

> > Bill



> > > Bill,

> > > It is undocumented but it works

> > > .Filter="FieldName=NULL"

> > > --
> > > Val Mazur
> > > Microsoft MVP



> > > > How the heck do I filter a recordset (already constructed) to select
> (or
> > > > exclude) records for which a particular field is Null? The obvious
> > filter
> > > > string, "foo Is Null" fails with an error and checking the docs on
ADO
> > it
> > > > appears you cannot specify Null as a value in a filter string!!

> > > > So, is it simply not possible to do this sort of filter in ADO?

> > > > Thanks in advance,
> > > >  Bill



Fri, 13 May 2005 21:40:42 GMT  
 Filter Recordset against Null?
Bill,

I would use that phrase in relation with LIKE clause not with simple filter
condition. K\Just tried and "FieldName=NULL" condition works fine

--
Val Mazur
Microsoft MVP


Quote:
> Val-
>   This is an Access app, not SQL Server. Actually documentation for ADO
> *DOES* say something about it.  In particular, (from the ADO Programmer's
> Reference, online help), it says:

> Value is the value with which you will compare the field values (for
> example, 'Smith', #8/24/95#, 12.345 or $50.00). Use single quotes with
> strings and pound signs (#) with dates. For numbers, you can use decimal
> points, dollar signs, and scientific notation. If Operator is LIKE, Value
> can use wildcards. Only the asterisk (*) and percent sign (%) wild cards
are
> allowed, and they must be the last character in the string. Value cannot
be
> Null.

> Note the last sentence in the above description.

> Bill



> > Bil,

> > Are you filtering in recordset or you need to filter in WHERE clause? In
> > case of Filter = Null should work and as I know works fine in several
> > applications. Actually documentation for ADO does not say anything about
> it.
> > You probably mix it with documentation for Transact-SQL. What is your
> code?
> > Which version of ADO are you using?

> > --
> > Val Mazur
> > Microsoft MVP



> > > Val-
> > >   Thanks, that does indeed work. It is not only undocumented, but
> > > contradicts the documentation!

> > > Bill



> > > > Bill,

> > > > It is undocumented but it works

> > > > .Filter="FieldName=NULL"

> > > > --
> > > > Val Mazur
> > > > Microsoft MVP



> > > > > How the heck do I filter a recordset (already constructed) to
select
> > (or
> > > > > exclude) records for which a particular field is Null? The obvious
> > > filter
> > > > > string, "foo Is Null" fails with an error and checking the docs on
> ADO
> > > it
> > > > > appears you cannot specify Null as a value in a filter string!!

> > > > > So, is it simply not possible to do this sort of filter in ADO?

> > > > > Thanks in advance,
> > > > >  Bill



Sat, 14 May 2005 20:35:24 GMT  
 Filter Recordset against Null?
Yes, the "Fieldname=Null" clearly works. The docs are ambiguous. Thanks for
the clarification of the docs.

Bill

Quote:
> Bill,

> I would use that phrase in relation with LIKE clause not with simple
filter
> condition. K\Just tried and "FieldName=NULL" condition works fine

> --
> Val Mazur
> Microsoft MVP



> > Val-
> >   This is an Access app, not SQL Server. Actually documentation for ADO
> > *DOES* say something about it.  In particular, (from the ADO
Programmer's
> > Reference, online help), it says:

> > Value is the value with which you will compare the field values (for
> > example, 'Smith', #8/24/95#, 12.345 or $50.00). Use single quotes with
> > strings and pound signs (#) with dates. For numbers, you can use decimal
> > points, dollar signs, and scientific notation. If Operator is LIKE,
Value
> > can use wildcards. Only the asterisk (*) and percent sign (%) wild cards
> are
> > allowed, and they must be the last character in the string. Value cannot
> be
> > Null.

> > Note the last sentence in the above description.

> > Bill



> > > Bil,

> > > Are you filtering in recordset or you need to filter in WHERE clause?
In
> > > case of Filter = Null should work and as I know works fine in several
> > > applications. Actually documentation for ADO does not say anything
about
> > it.
> > > You probably mix it with documentation for Transact-SQL. What is your
> > code?
> > > Which version of ADO are you using?

> > > --
> > > Val Mazur
> > > Microsoft MVP



> > > > Val-
> > > >   Thanks, that does indeed work. It is not only undocumented, but
> > > > contradicts the documentation!

> > > > Bill



> > > > > Bill,

> > > > > It is undocumented but it works

> > > > > .Filter="FieldName=NULL"

> > > > > --
> > > > > Val Mazur
> > > > > Microsoft MVP



> > > > > > How the heck do I filter a recordset (already constructed) to
> select
> > > (or
> > > > > > exclude) records for which a particular field is Null? The
obvious
> > > > filter
> > > > > > string, "foo Is Null" fails with an error and checking the docs
on
> > ADO
> > > > it
> > > > > > appears you cannot specify Null as a value in a filter string!!

> > > > > > So, is it simply not possible to do this sort of filter in ADO?

> > > > > > Thanks in advance,
> > > > > >  Bill



Sat, 14 May 2005 23:16:46 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. filter recordset for Null value

2. Newcomer: Filtering a dropdown list against another on a web page

3. Recorset.Filter using wildcards against an Access DB

4. Recorset.Filter using wildcards against an Access DB

5. FILTERING RECORDSET BASED ON ANOTHER RECORDSET

6. Filtering Single recordset vs creating multiple recordsets

7. get a new recordset based on a filtered recordset

8. Xtract a recordset from an existing recordset w/out using Filter

9. Query against disconnected recordset

10. Check fot null-values in a find/filter of an ADO-recorset

11. Nulls in ADO Filters

12. Run SQL Statement Against Disconnected ADO Recordset

 

 
Powered by phpBB® Forum Software