NULL values in Access (follow up on WHERE NOT)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

NULL values in Access (follow up on WHERE NOT)

Author

Topic

EricBHK
Starting Member

18 Posts

Posted - 2012-08-17 : 13:09:32

This is a follow up of my earlier post called 'WHERE NOT'

In the meantime, I found out that the error is caused by unhandled NULL Values in columns Keyword1 ... to Keyword10.

So I am now looking out for sthe correct syntax in the line of :

CopySql21 = "DELETE * FROM LiveSearch WHERE Keyword1 <> ('" & ComboBox1.Text & "' OR " "), AND Keyword1 <> ('" & ComboBox2.Text & "' OR " "), ... and so on.

Since this is clearly not the proper syntax, I keep on searching ...

Help still appreciated after a long day.

Thanks !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 13:16:53

why do you need extra ) in between? shouldnt it be

CopySql21 = "DELETE * FROM LiveSearch WHERE Keyword1 NOT IN ('" & ComboBox1.Text & "','" & ComboBox2.Text & "') OR Keyword1 = ''".... and so on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

EricBHK
Starting Member

18 Posts

Posted - 2012-08-17 : 13:49:48

I don't think that is going to work for the same reason my initial sql string won't work : the double "" are dissected by sql as follws :
a. the first " is taken as the end of the string
b. the second " is considered to be outside the sql string

so i guess I will need something more robust like <>NULL or maybe <>ISNULL

I am working on it, but pls keep your comments flowing !

LoL

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-17 : 13:59:44

What do you want to do when Keyword1 is null? Would this do it?

CopySql21 = "DELETE * FROM LiveSearch WHERE Keyword1 IS NOT NULL AND Keyword1 <> ('" & ComboBox1.Text & "' OR " "), AND

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 14:00:50

quote:
Originally posted by EricBHK

I don't think that is going to work for the same reason my initial sql string won't work : the double "" are dissected by sql as follws :
a. the first " is taken as the end of the string
b. the second " is considered to be outside the sql string

so i guess I will need something more robust like <>NULL or maybe <>ISNULL

I am working on it, but pls keep your comments flowing !

LoL

nope thats wrong
its not "
but what i suggested is two ' s inside string and a further " to close the string

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

EricBHK
Starting Member

18 Posts

Posted - 2012-08-17 : 14:22:32

Dear sunitabeck,

your tip put me straight in the right direction.

I want to delete rows that do not contain at least one field where field value = my combobox.text.
In other words : rows that do contain only other values AND/OR blanks (NULL values) have to be deleted.

I made a minor change to your example as follows :

CopySql21 = "DELETE * FROM LiveSearch WHERE (Keyword1 IS NULL OR Keyword1 <> '" & ComboBox1.Text & "') AND (Keyword2 IS NULL OR Keyword2 <> '" & ComboBox1.Text & "') AND ... and so on

Now it is working perfectly. Still need to do more elaborated testing, but I do not expect any further problems.

Thank you all for your contributions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 14:39:49

hmm...so were there two Keyword fields? first statement was including same column for all the checks

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-08-17 : 15:09:06

PICNIC

Problem
In
Chair
Not
In
Computer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources