Delete records with Null values

[Tep]

Hi,

I would like to make a listbox that on double click will delete the concerning record.
.

This works fine for records with both a value for [FoodEx1_hier_cd] and for [Ingredient], but it does not work for records with a Null value for [Ingredient].
THis is the code:

If IsNull(Me.List53.Column(1)) = False Then
CurrentDb.Execute "DELETE * FROM [FoodComposition FE1] WHERE FoodEx1_hier_cd LIKE '" & Me.List53.Column(0) & "' AND Ingredient LIKE '" & Me.List53.Column(1) & "';"
End If
If IsNull(Me.List53.Column(1)) = True Or (Me.List53.Column(1)) Like "" Then
CurrentDb.Execute "DELETE * FROM [FoodComposition FE1] WHERE FoodEx1_hier_cd LIKE '" & Me.List53.Column(0) & "' AND (Me.List53.Column(1) IS NULL Or Me.List53.Column(1) Like "");"
End If
Me.List53.Requery

The error I get is:

Run-time error 3075: Syntax error in string expression 'FoodEx1_hier_cd LIKE 'A.04.02' AND (Me.List53.Column(1) Is NULL Or Me.List53.Column(1) Like '');'.

Has anybody any idea why this does not delete the records with Null values (or value '')?

Thank you in advance!
Tep

 

[pr2-eugin]

Tep, Should the DELETE not be..

CurrentDb.Execute "DELETE * FROM [FoodComposition FE1] WHERE FoodEx1_hier_cd LIKE '" & Me.List53.Column(0) & "' AND ([COLOR=Red][B]Ingredient[/B][/COLOR] IS NULL Or [COLOR=Red][B]Ingredient[/B][/COLOR] Like "");"

 

[Tep]

Thank you Paul. You are right, it should be Ingredient in the delete statement. It is a pity however that I get the same error... Any other ideas...?
Thanks.

 

[pr2-eugin]

Yup, found the error..

CurrentDb.Execute "DELETE * FROM [FoodComposition FE1] WHERE FoodEx1_hier_cd LIKE '" & Me.List53.Column(0) & "' AND (Ingredient IS NULL Or Ingredient Like [COLOR=Red][B]''[/B][/COLOR]);"

However, my question is, why have you used LIKE operator? As you are getting the values from a ComboBox, there is no worry of mistyping or remembering only part of the String.. You would be better of using EQUALS (=).. The main reason being LIKE operator (1) discards any index you have, (2) the search results are not always reliable.. Considering that, the Query could be..

CurrentDb.Execute "DELETE * FROM [FoodComposition FE1] WHERE FoodEx1_hier_cd [COLOR=Red][B]=[/B][/COLOR] '" & Me.List53.Column(0) & "' AND (Ingredient IS NULL Or Ingredient[COLOR=Red][B] =[/B][/COLOR] '');"

 

[Tep]

I got it. This work:

CurrentDb.Execute "DELETE * FROM [FoodComposition FE1] WHERE FoodEx1_hier_cd LIKE '" & Me.List53.Column(0) & "' AND (IsNull(Ingredient) = True OR Ingredient LIKE '');"[CODE/]
 
Thanks.

 

[Tep]

Oke Paul. Thank you. The part of '' in stead of "" I should indeed also change. And the = in stead of like is also a good suggestion. Thank you again!

 

[pr2-eugin]

Good you have that working, but I would suggest you look into using LIKE here also using IsNull..

* Is Null is different from IsNull, in the words of Pat,

To answer your other question IsNull() is a VBA function but "Is Null" is SQL syntax. It probably doesn't matter in this case but I don't use VBA functions when SQL has the necessary language element. There is no reason to force the SQL engine to invoke the VBA library unnecessarily.

You were right about using Is Null.. Just stick with it..

EDIT: Saw that you have made the reply, before seeing my suggestion.. Good for you..