Delete records with null values

[Tep]

Hi,

I am trying to delete a selected record with the following code:

Private Sub BtDelRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click
Dim strSQL As String
   strSQL = "DELETE * FROM [dbo_Conversie_lokatie] WHERE [dbo_Conversie_lokatie].[CONVERSIEPROG_NAAM] = '" & Me![Listbox].Column(0) & "' AND [dbo_Conversie_lokatie].[PLAATS_EXTERN] = '" & Me![Listbox].Column(1) & "' AND [dbo_Conversie_lokatie].[LAND_CODE] = '" & Me![Listbox].Column(2) & "' AND [dbo_Conversie_lokatie].[PROVINCIE_CODE] = '" & Me![Listbox].Column(4) & "' AND [dbo_Conversie_lokatie].[LOKATIE_NUMMER] = " & Me![Listbox].Column(6) & " "
   DoCmd.RunSQL strSQL
   Me![Listbox].Requery
  
Exit_cmdSaveRecord_Click:
  
Err_cmdSaveRecord_Click:
       Exit Sub
   MsgBox Err.Description
   Resume Exit_cmdSaveRecord_Click
End Sub

For records with values for all the variables there is no problem.
But there are records in the table dbo_Conversie_lokatie and in the Listbox with null values.
These records will not be deleted with the code.
If Me![Listbox].Column(4) is null, there is the message: "you are about to delete 0 rows from the specified table". (it should be 1 row).
If Me![Listbox].Column(6) is null, there is no error message. From the Docmd it goes directly to End sub but does not delete the record.
How can I change the code that the null values are deleted as well?

I tried soemthing with IsNull, but it does not work either. I changed the sSQL:

strSQL = "DELETE * FROM [dbo_Conversie_lokatie] WHERE [dbo_Conversie_lokatie].[CONVERSIEPROG_NAAM] = '" & Me![Listbox].Column(0) & "' AND [dbo_Conversie_lokatie].[PLAATS_EXTERN] = '" & Me![Listbox].Column(1) & "' AND [dbo_Conversie_lokatie].[LAND_CODE] = '" & Me![Listbox].Column(2) & "' AND IsNull ([dbo_Conversie_lokatie].[PROVINCIE_CODE]) = '" & IsNull ((Me![Listbox].Column(4))) & "' AND [dbo_Conversie_lokatie].[LOKATIE_NUMMER] = " & Me![Listbox].Column(6) & " "

When only [PROVINCIE_CODE] and (Me![Listbox].Column(4)) is null now it tries to delete 0 rows again, while it should be 1 row.

I hope somebode can help me!
Thanks,
Tep

 

[Uncle Gizmo]

The best approach is to write a select query which returns the records that you want to delete. From this select query you will see whether you are returning the correct records.

Some records that you may think are Null many in fact be empty strings “”, again running a select query and seeing the results will help you find problems like this.

 

[Tep]

Thank you for your advice. I am not sure whether a select query would make the difference, while I already can see which records I select in the listbox.

Anyhow, I have been trying to make the query work and this is what is working now:

Dim strSQL As String
If Len(Me![Listbox].Column(4) & "") = 0 And (Len(Me![Listbox].Column(6) & "") <> 0) Then
strSQL = "DELETE * FROM [dbo_Conversie_lokatie] WHERE [dbo_Conversie_lokatie].[CONVERSIEPROG_NAAM] = '" & Me![Listbox].Column(0) & "' AND [dbo_Conversie_lokatie].[PLAATS_EXTERN] = '" & Me![Listbox].Column(1) & "' AND [dbo_Conversie_lokatie].[LAND_CODE] = '" & Me![Listbox].Column(2) & "' AND IsNull ([dbo_Conversie_lokatie].[PROVINCIE_CODE])= True AND [dbo_Conversie_lokatie].[LOKATIE_NUMMER] = " & (Me![Listbox].Column(6)) & " "
'strSQL = "DELETE * FROM [dbo_Conversie_lokatie] WHERE [dbo_Conversie_lokatie].[CONVERSIEPROG_NAAM] = '" & Me![Listbox].Column(0) & "' AND [dbo_Conversie_lokatie].[PLAATS_EXTERN] = '" & Me![Listbox].Column(1) & "' AND [dbo_Conversie_lokatie].[LAND_CODE] = '" & Me![Listbox].Column(2) & "' AND IsNull ([dbo_Conversie_lokatie].[PROVINCIE_CODE]) = '" & IsNull((Me![Listbox].Column(4))) & "' AND IsNull ([dbo_Conversie_lokatie].[LOKATIE_NUMMER]) = " & IsNull(Me![Listbox].Column(6)) & " "
End If
If (Len(Me![Listbox].Column(4) & "") = 0) And (Len(Me![Listbox].Column(6) & "") = 0) Then
strSQL = "DELETE * FROM [dbo_Conversie_lokatie] WHERE [dbo_Conversie_lokatie].[CONVERSIEPROG_NAAM] = '" & Me![Listbox].Column(0) & "' AND [dbo_Conversie_lokatie].[PLAATS_EXTERN] = '" & Me![Listbox].Column(1) & "' AND [dbo_Conversie_lokatie].[LAND_CODE] = '" & Me![Listbox].Column(2) & "' AND IsNull ([dbo_Conversie_lokatie].[PROVINCIE_CODE])= True AND IsNull ([dbo_Conversie_lokatie].[LOKATIE_NUMMER]) = True  "
End If
If (Len(Me![Listbox].Column(6) & "") = 0) And (Len(Me![Listbox].Column(4) & "") <> 0) Then
strSQL = "DELETE * FROM [dbo_Conversie_lokatie] WHERE [dbo_Conversie_lokatie].[CONVERSIEPROG_NAAM] = '" & Me![Listbox].Column(0) & "' AND [dbo_Conversie_lokatie].[PLAATS_EXTERN] = '" & Me![Listbox].Column(1) & "' AND [dbo_Conversie_lokatie].[LAND_CODE] = '" & Me![Listbox].Column(2) & "' AND ([dbo_Conversie_lokatie].[PROVINCIE_CODE]) = '" & ((Me![Listbox].Column(4))) & "'  AND IsNull ([dbo_Conversie_lokatie].[LOKATIE_NUMMER]) = True  "
End If
If (Len(Me![Listbox].Column(4) & "") <> 0) And (Len(Me![Listbox].Column(6) & "") <> 0) Then
strSQL = "DELETE * FROM [dbo_Conversie_lokatie] WHERE [dbo_Conversie_lokatie].[CONVERSIEPROG_NAAM] = '" & Me![Listbox].Column(0) & "' AND [dbo_Conversie_lokatie].[PLAATS_EXTERN] = '" & Me![Listbox].Column(1) & "' AND [dbo_Conversie_lokatie].[LAND_CODE] = '" & Me![Listbox].Column(2) & "' AND ([dbo_Conversie_lokatie].[PROVINCIE_CODE]) = '" & ((Me![Listbox].Column(4))) & "'  AND [dbo_Conversie_lokatie].[LOKATIE_NUMMER] = " & (Me![Listbox].Column(6)) & " "
End If
   DoCmd.RunSQL strSQL
   Me![Listbox].Requery

Maybe it is not the most neat solution, but I think it works well enough.
Thanks