Hi everybody,

i have 15 records in an ACCESS database ... each have 4 fields

in last 3 records the third field named "lnkdisp" has been left empty

i am using following string to open the RECORDSET object

sql1="select * from odclinks where lnkdisp="""" ;"

if i do RESPONSE.WRITE(sql1) then i get following output
-----
select * from odclinks where lnkdisp="" ;
-----

however when i open the RECORDSET object no records are retrieved ...

is it that ACCESS treats NULL values differently

plz help ...

Sudhan.

__________________
--------------------------------------------------
Sudhan Kanitkar.
Everything Is In Our Hands.

I think you need to use single quotes in SQL Queries, even for access, but I'm not 100% on that, I dont use access much.

"select * from odclinks where lnkdisp=''"

Simillarly I'n not sure on how nulls are treated but a little trial and error should work it out one way or another.

You can try
"select * from odclinks where lnkdisp='' or lnkdisp is not null"


======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================

Thanks Rod,

this one worked

select * from odclinks where lnkdisp is NULL;

so for the time being i am ready to conclude that to compare NULL values in ACCESS one should always compare using the word "NULL" itself rather than empty single/double QUOTES...

thanks again...

Sudhan.

Hi Sudhan,

There is a difference in Access between Null and a Zero Length String.

Check out this thread: http://p2p.wrox.com/topic.asp?TOPIC_ID=11015
One of my posts near the end explains the difference between the two.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Yeah, Sorry I meant is null, not is not null.

You really need to do both

Because nothing and null are not the same

is null will not return empty strings
and
'' will not return nulls

Try it out put a few nulls in and a few empty string and see the difference.

======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================

Hi rod and imar,

how doesn one input empty strings into ACCESS database directly ....

Sudhan.

HI imar & Rod,

imar i read the previous forum you linked out for me ... thats brilliant .... now enverything is crystal clear in my head ....

and also like rod said i have to use both NULL and '' in the comparision ....
thanks both of you ...

and i think a simple IF...ELSE will be enough for me to know whether the value is NULL or '' just in case i need to know ... ofcourse i dont need to know in the current application ... but i think its good to remember about it ...

well keep in touch ..

Sudhan.

In ASP you can use this trick:

If MyRecordset("MyField") & "" = "" Then

End If

This appends a zero length string (ZLS) to the field from the database. Null + a ZLS equals a ZLS, so you can compare it against another ZLS ("").

Alternatively, you could check the length:

If Len(MyRecordset("MyField")) = 0 Then

End If

This would return True for null and ZLS fields.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Yep Imar,

That exactly what i had in mind ... now i am sure that its gonna work that way ..

Sudhan.