Question SQL Delete Problem

[byTimber]

Struggling with this one:

I need an SQL statement to delete all records in a recordset which have an empty field (text field).

strSQL = "DELETE FROM [tblLVRWrittenStatements] " & _
"WHERE [clientName] = '" & "" & "'"
CurrentDb.Execute strSQL, dbFailOnError

I have tried with (above) and without square brackets and all sorts of combinations of quotes.

Where am I going wrong?
Help!!!!!!!!

 

[Trevor G]

Have you added a debug statement to see what the Immedaite window shows you. It also maybe that you need a space before your first speach mark

strSQL = " DELETE FROM [tblLVRWrittenStatements] " & _
" WHERE [clientName] = '" & "" & "'"
CurrentDb.Execute strSQL, dbFailOnError
Debug.Print strSQL

 

[byTimber]

Thanks for reply Trevor but I have tried;

'strSQL = "DELETE FROM [tblLVRWrittenStatements] " & _
'"WHERE [clientName] = '" & "" & "'"
'CurrentDb.Execute strSQL, dbFailOnError

DeBug says: DELETE FROM [tblLVRWrittenStatements] WHERE [clientName] = ' '
-----------------------------------------------------------------------------
strSQL = "DELETE FROM [tblLVRWrittenStatements] " & _
"WHERE [clientName] = """""
CurrentDb.Execute strSQL, dbFailOnError

DeBug says DELETE FROM [tblLVRWrittenStatements] WHERE [clientName] = ""

But all these records with empty fields are still in the table!

 

[Trevor G]

What happens if you create a normal Query and select the Table and field and add the criteria is null. you would then get the SQL code, which you can adapt.

DELETE tblLVRwrittenStatements.Customer, tblLVRwrittenStatements.ClientName
FROM tblLVRwrittenStatements
WHERE (((tblLVRwrittenStatements.ClientName) Is Null));

 

[Galaxiom]

Expanding on Trevor's post:

A text field can have two different types of empty, the Null and Zero Length String.
Null is the absence of a value. The ZLS is a value with no characters.

Null cannot be equated to anything and requires Is Null as the test in SQL or the IsNull() function in VBA. Once this is appreciated the handling of empty text fields starts to make sense.

 

[byTimber]

Thank you both for suggestions Null noted.

I have copied in Trevor's code but I have a red line not accepted .
Does there need to be underscores somewhere?
strSQL to do this must be around. I am all at sea (metaphorically speaking only - sadly!)

 

[Trevor G]

Here you go this should work for you

Sub runQry1()
Dim strSQL As String
strSQL = " DELETE tblLVRwrittenStatements.ClientName"
strSQL = strSQL & " FROM tblLVRwrittenStatements"
strSQL = strSQL & " WHERE (((tblLVRwrittenStatements.ClientName) Is Null));"
CurrentDb.Execute strSQL, dbFailOnError
End Sub

 

[byTimber]

Trevor THANK YOU! Who invented this SQL never took into account the resulting deterioration of users brain cells. All good experience. Thanks for your time and on such a lovely day as well .....

 

[RainLover]

What happens if you create a normal Query and select the Table and field and add the criteria is null. you would then get the SQL code, which you can adapt.

This advice by Trevor is sound.

More people should take notice of it.

 

[Trevor G]

Trevor THANK YOU! Who invented this SQL never took into account the resulting deterioration of users brain cells. All good experience. Thanks for your time and on such a lovely day as well .....

Happy to help and thanks for the note, and also for the feedback. Beautifull day indeed.

 

[Trevor G]

This advice by Trevor is sound.

More people should take notice of it.

Thanks for that Rain.

 

[RainLover]

Trevor

I have been using this sort of stuff for years.

It is just habit for me to do it your way. Yet these new people write complex statements and wonder why it doesn't work.

I see no reason to do it the hard way.

 

[Trevor G]

I fully agree Rain,

But always nice to help someone understand a method that is more productive.

 

[Galaxiom]

I have been using this sort of stuff for years.

It is just habit for me to do it your way. Yet these new people write complex statements and wonder why it doesn't work.

I see no reason to do it the hard way.

It isn't about "the hard way" but not knowing that Null isn't the same as an empty string.

Unfortunately, due to the discrete nature of human consciousness it doesn't matter how many times you have done something, that knowledge is not automatically available to others. That is why we have this forum.

Your habits are built on your experience and I have no doubt you didn't automatically know everything when you first started either.

 

[RainLover]

Galaxiom

Are you really suggesting that a newbee should go straight to free typing SQL rather than use Queries as Microsoft intended.

 

[Galaxiom]

strSQL = " DELETE tblLVRwrittenStatements.ClientName"
strSQL = strSQL & " FROM tblLVRwrittenStatements"
strSQL = strSQL & " WHERE (((tblLVRwrittenStatements.ClientName) Is Null));"

Personally I am not a fan of what I call "iterative contatenation" but prefer continuation. It is uncluttered by the needless repetition of the variable name, better for layout and much clearer to read.

strSQL = "DELETE tblLVRwrittenStatements.ClientName" _
       & " FROM tblLVRwrittenStatements" _
       & " WHERE tblLVRwrittenStatements.ClientName Is Null;"

(I also removed the unnecessary parentheses that Access includes)

Continuation has a 22 line limit but that should be more than enough to build very complex strings. If more are required then use contination for sections and concatenate them together.

 

[Galaxiom]

Are you really suggesting that a newbee should go straight to free typing SQL rather than use Queries as Microsoft intended.

Ahh. I musunderstood the context from further back along the chain.

What happens if you create a normal Query

Indeed when I started writing SQL I would make a query in the designer and copy it to the VB editor. Still do sometimes to save typing on big queries.

 

[Galaxiom]

I will never understand why people insist on typing SQL strings in VBA when they can build the queries and save them where they can be reused and built upon.

That sounds like a very self-limiting statement.

Queries in code can be constructed dynamically allowing for possiblities not supported in a stored query.

Stored queries work well where criteria are built by direct references to controls on forms but this virtually eliminates portability. The alternative of adding parameters to the query involves code too so can be complex as simply concatenating values straight into an SQL string.

Queries in code can be completely isolated from the eyes of a prying user.

When working on a procedure there is no need to go off looking for a query because it is right there in the code. This also enhances portablity of the object because it does not rely on a fixed external query. The code can even be designed to work with a completely different table that can be loaded as a property of the object. Stored queries cannot possibly compete on this level.

Learning SQL is a natural progression for developers who want to work with database servers or construct applications that don't rely on Access.

 

[byTimber]

Sorry that my question has produced such dissent amongst such a high powered peer group - all these replies have been noted and have been extremely helpful to this new access user - thank you all .....

 

[RainLover]

Sorry that my question has produced such dissent amongst such a high powered peer group - all these replies have been noted and have been extremely helpful to this new access user - thank you all .....

Don't worry about the little banter.

Galaxiom often throws his opinion in but luckily there is Pat who would be the second most knowlegable person on this site.