rs.Filter question

[Sideman]

Hi everyone,
I'm using VB6(SP5), Access2000, ADO data control and populating a recordset with a simple SQL query..."SELECT * FROM Parts". I'm using option buttons to allow the user to select various columns to display in a DataCombo box where they will then select the specific item data they wish to view in the bound text boxes of the form. All pretty basic stuff.
I'm having trouble using the recordset.filter. What I want to do is something like...
rs.Filter="NewPartNum IS NOT NULL"
rs.Sort="NewPartNum"
I need to filter out Null fields, but I can't seem to get it to work. I know the query can do it, but I need to do this many times depending on the user's option button selection. Thus the Filter property would be ideal. Rather than running a new query for each situation.
Could anyone help me out...as in explain how this property should be used or where I can find good information about it. I have searched the forum and the FAQs to no avail, and MS Help didn't seem to "help" either.

Of all the things I've lost, I miss my mind the most!
Sideman

 

[dilettante]

The Filter property syntax is:

field relationship value

Where &quot;IS NOT&quot; is not a valid option (use &quot;<>&quot and the value cannot be null.

I think you are stuck and need another approach.

http://msdn.microsoft.com/library/d...y/en-us/ado270/htm/mdapro03_10.asp?frame=true

 

[Sideman]

Thanks, dilettante.
I'm sorry to hear that this won't work. I had high hopes...but then again, I am dealing with Microsoft.

Any suggestions on another approach??

Of all the things I've lost, I miss my mind the most!
Sideman

 

[CCLINT]

On the Filter method use:

rs.Filter=&quot;NewPartNum <> NULL&quot;

(DO NOT use this in an sql statement, but use &quot;...WHERE NOT NewPartNum IS NULL&quot; instead.

 

[Sideman]

Sorry, I've been away on other business.

CCLINT
I tried your suggestion, but the NULL value won't work in the Filter property. I'm now experimenting with stored queries in Access 2000 (using ADO Data Controls)...much faster than executing an SQL statement for each Option Button choice. I'll let you know how it comes out.

Thanks for your help.

Of all the things I've lost, I miss my mind the most!
Sideman