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

 

[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