Delete table records with SQL when field is empty

[Carl23]

I have tables imported into Access titled: sheet1, sheet2, etc. There are 4 fields in each table-resident, physician, comment and physician_comment. I am trying to write a SQL delete query to allow the user to 1. select a sheet (table) then 2. delete rows in the table where the physician_comment is empty. The code reads as follows:

Private Sub Command28_Click()
   Dim StrSql As String
   Dim tbl As String
   MyValue1 = InputBox("Enter File Number", "MyInputbox")
   MsgBox "Sheet" & MyValue1
   tbl = "Sheet" & MyValue1
   StrSql = "DELETE FROM tbl WHERE tbl.PhysicianComment,"""
   DoCmd.RunSQL StrSql
End Sub
 
 

[Stewart Ross]

You need to include the value of variable tbl in your SQL string. Also, your DELETE statement is missing its 'all records' wildcard (*), and your WHERE clause will not work as it stands.

Assuming that the PhysicianComment field is null when empty (that is, it does not contain any value at all, so it does not evaluate to an empty string) replace line 7 above as follows:

StrSql = "DELETE * FROM [" & tbl & "] WHERE PhysicianComment IS NULL"

If, as I doubt will be the case, PhysicianComment really is an empty string when there is no value, try:

StrSql = "DELETE * FROM [" & tbl & "] WHERE PhysicianComment = ''"

I note that your post does not show a consistent name for the field PhysicianComment. In your code it is as shown here, but in your text it is referred to as physician_comment instead. This is a crucial point - SQL statements do not tolerate errors in field or table names.

If in fact your field is named physician_comment then you MUST use that version in your SQL statement. If your field is actually named Physician Comment (with the space as shown) then you need to enclose the field name in square brackets, like this: [Physician Comment]

-Stewart