And Statement in Where Not Working 2

[JackD4ME]

hi everybody.

i am trying to filter a recordset using the where statement with 2 criteria. when i use 'and' it doesnt filter and when i use the '&' i get no results. here is the code:

sTr="Select * From Drainage Where [Parcel Number]='" & Request.Cookies("PIN") & "' and [Deleted]<>'Yes' Order by DMR"

does anyone know why this is not filtering results?
thanks in advance.

Jack

 

[cLFlaVA]

Response.Write your SQL statement. Odds are there's no value in your cookie.



*cLFlaVA
----------------------------
[tt]somebody set up us the bomb![/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[DotNetGnat]

do a response.write on your query and see how it looks like...

may be the database has a 1 for Yes and 0 for No in the field...check your db...

besides that,there is nothing wrong with the query...

-DNG

 

[JackD4ME]

ok, i have narrowed it down. for some ODD reason it does not like the <>. i changed the [Deleted] field to [Del] and this is a text field. So the options are nothing or Yes. but it only finds the Yes records. I have changed the coding to read '' and null but neither work. The cookie is displayed on the page so i know it is there. There are 4 records; 2 w/Yes and 2/nothing.

This code returns the results i do not want:

sTr="Select * From Drainage Where [Parcel Number]='" & Request.Cookies("PIN") & "' and [Del]=Yes Order by DMR"

This code returns no results (and it does not matter if Yes is in single quotes:

Select * From Drainage Where [Parcel Number]='0101001001' and [Del]<>Yes Order by DMR

 

[DotNetGnat]

try this:

Select * From Drainage Where [Parcel Number]='0101001001' and [Del]!=Yes Order by DMR

-DNG

 

[cLFlaVA]

when testing for null, you need to say:

... where field is null ...

or

... where field is not null ...

not

... where field = null ...

null means no value, so therefore you can't do comparisons against no value.



*cLFlaVA
----------------------------
[tt]somebody set up us the bomb![/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[DotNetGnat]

try

fieldname IS NULL
OR
ISNULL(fieldname)


-DNG

 

[cLFlaVA]

DNG, do you have no regard for what others have posted?



*cLFlaVA
----------------------------
[tt]somebody set up us the bomb![/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[DotNetGnat]

sorry...i havent seen your post before posting..i had the reply window open for a long time...

-DNG

 

[cLFlaVA]

gotcha.



*cLFlaVA
----------------------------
[tt]somebody set up us the bomb![/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[JackD4ME]

THANK YOU, everybody!!!!! It was the IsNull(Del) that worked. Somtimes i hate doing this!

Jack