unable to filter records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello I have the code below to filter a recordset trough code
It seems not working properly...actually..not working at all.
And it seems the problem are this section of the line: VISIT IS NOT NULL AND
TOTHAM_17ITEMS IS NOT NULL

I have tested it with the query builder and it works perfectly! Why it
shouldn't work also when used in code?

Code here:
rst.Filter = "ID=" & IDs.Fields(0) & " AND VISIT<>'0' AND VISIT
IS NOT NULL AND TOTHAM_17ITEMS IS NOT NULL"

Thanks,
Rocco
 
Hello I have the code below to filter a recordset trough code
It seems not working properly...actually..not working at all.
And it seems the problem are this section of the line: VISIT IS NOT NULL AND
TOTHAM_17ITEMS IS NOT NULL

I have tested it with the query builder and it works perfectly! Why it
shouldn't work also when used in code?

Code here:
rst.Filter = "ID=" & IDs.Fields(0) & " AND VISIT<>'0' AND VISIT
IS NOT NULL AND TOTHAM_17ITEMS IS NOT NULL"
Click to expand...

If Visit is a Number type field, you need to lose the quotemarks
around '0'. Note that a criterion of VISIT <> '0' or VISIT <> 0 will
exclude NULLs already - NULL is not equal to 0 but it's not UNequal
either!


John W. Vinson[MVP]
 
Actually the fields VISIT doens't store nunbers. So I need to use the quote
mark.
Anyway the problem is TOTHAM_17ITEMS IS NOT NULL.

I mean this sentence works perfectly when used in the query builder but it
gives error when used in code.
If I delete this part: VISIT IS NOT NULL AND TOTHAM_17ITEMS IS NOT NULL it
works also in code.
I think there is something about the IS NOT NULL issue that wont be accepted
in code...
 
Actually the fields VISIT doens't store nunbers. So I need to use the quote
mark.
Anyway the problem is TOTHAM_17ITEMS IS NOT NULL.

I mean this sentence works perfectly when used in the query builder but it
gives error when used in code.
If I delete this part: VISIT IS NOT NULL AND TOTHAM_17ITEMS IS NOT NULL it
works also in code.
I think there is something about the IS NOT NULL issue that wont be accepted
in code...
Click to expand...

Nope, that should work fine. Is there in fact a field named
TOTHAM_17ITEMS in the Form's Recordsource? Might there be a zero
instead of a letter O in the "TO", or some other misspelling? Is the
underscore actually a blank, in which case you'll need

[TOTHAM 17ITEMS]

It may be helpful to put all fieldnames in brackets in any case, just
as a matter of good practice... but I don't see why this should be
causing an error unless there's something invalid about the fieldname.

John W. Vinson[MVP]
 
I know it should work! But i doesn't!!
I have used exactly the same strin with the query builder and it works
perfectly fine.
I have changed my code.

Instead of this:
rst.Open "[DM_TOTHAM17 AT ALL VISITS]", CurrentProject.Connection,
adOpenStatic, adLockReadOnly
rst.Filter = "ID=" & IDs.Fields(0) & " AND VISIT<>'0' AND VISIT IS NOT NULL
AND TOTHAM_17ITEMS IS NOT NULL"

I have used this:
rst.Open "SELECT * FROM [DM_TOTHAM17 AT ALL VISITS] WHERE ID=" &
IDs.Fields(0) & " AND VISIT<>'0' AND VISIT IS NOT NULL AND TOTHAM_17ITEMS IS
NOT NULL", CurrentProject.Connection, adOpenStatic, adLockReadOnly

and it works exactly as it should do. Weird, uh?
I really doesn't understand why it wont work with the Filter method. Using
the changed code I' m able to achieve my goals, nut it would be very
intersting to know why the Filter method gives the error with a string that
doesn't show any sintax mistake.
So I will leave the post hoping someone will give us an answer.
Thanks anyway, I appreciate your help.

Rocco



John Vinson said:
Actually the fields VISIT doens't store nunbers. So I need to use the quote
mark.
Anyway the problem is TOTHAM_17ITEMS IS NOT NULL.

I mean this sentence works perfectly when used in the query builder but it
gives error when used in code.
If I delete this part: VISIT IS NOT NULL AND TOTHAM_17ITEMS IS NOT NULL it
works also in code.
I think there is something about the IS NOT NULL issue that wont be accepted
in code...
Click to expand...

Nope, that should work fine. Is there in fact a field named
TOTHAM_17ITEMS in the Form's Recordsource? Might there be a zero
instead of a letter O in the "TO", or some other misspelling? Is the
underscore actually a blank, in which case you'll need

[TOTHAM 17ITEMS]

It may be helpful to put all fieldnames in brackets in any case, just
as a matter of good practice... but I don't see why this should be
causing an error unless there's something invalid about the fieldname.

John W. Vinson[MVP]
Click to expand...
 
Back
Top