Solved-Trying to Delete empty records!

[turniporange23]

Any reason the delete code dose not work When I shut down my database .. Thanks Bob

Private Sub cmdQuitSlow_Click()
'Me.ckbUpdate = True
'Forms!frmMain.Requery
DoCmd.SetWarnings False
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryOrphanAddCharges"
DoCmd.OpenQuery "qryOrphanDaily"
DoCmd.OpenQuery "qryOrphanAddChargesInter"
DoCmd.OpenQuery "qryDeletedHorseDetails"
DoCmd.OpenQuery "QryDeleteDupHorseID"
DoCmd.OpenQuery "QryDeleteNoHorseID"
DoCmd.OpenQuery "QryDeleteInvoice"
DoCmd.OpenQuery "QryDeleteInvoiceNoNumber"
DoCmd.OpenQuery "QryDeleteChargeNoAmount"
DoCmd.OpenQuery "QryDeleteChargeNoID"
DoCmd.OpenQuery "QryDeleteHorseNoName"
DoCmd.OpenQuery "qryDeleteNoDetails"

DoCmd.SetWarnings True

TestBackup
DoCmd.Quit
End Sub

 

[DHookom]

So the fields actually are numeric and store 0s. Zeros are values, not blanks and the should not be compared to a string.

 

[Gasman]

If Isladog's SQL works, then some of those 4 fields have Null and some have empty string.

That is going to cause you no end of problems like this one.
You should settle on one or the other.

 

[isladogs]

That is going to cause you no end of problems like this one.
You should settle on one or the other.

The results in post #9 based on June’s query clearly showed there are both zero length strings and null values. That was why the Nz(field name, “”) filters worked.

@Duane
Just wondering why you thought the fields contained numbers. Possibly due to #9 which threw me at first

 

[isladogs]

Any reason the delete code dose not work When I shut down my database .. Thanks Bob

Private Sub cmdQuitSlow_Click()
'Me.ckbUpdate = True
'Forms!frmMain.Requery
DoCmd.SetWarnings False
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryOrphanAddCharges"
DoCmd.OpenQuery "qryOrphanDaily"
DoCmd.OpenQuery "qryOrphanAddChargesInter"
DoCmd.OpenQuery "qryDeletedHorseDetails"
DoCmd.OpenQuery "QryDeleteDupHorseID"
DoCmd.OpenQuery "QryDeleteNoHorseID"
DoCmd.OpenQuery "QryDeleteInvoice"
DoCmd.OpenQuery "QryDeleteInvoiceNoNumber"
DoCmd.OpenQuery "QryDeleteChargeNoAmount"
DoCmd.OpenQuery "QryDeleteChargeNoID"
DoCmd.OpenQuery "QryDeleteHorseNoName"
DoCmd.OpenQuery "qryDeleteNoDetails"

DoCmd.SetWarnings True

TestBackup
DoCmd.Quit
End Sub

First of all you don’t need to set warnings false twice. Delete one of those.
Secondly, for testing purposes, disable the set warnings false line and see what happens. Do all the queries run show a message about the number of records that will be deleted?

Does it work if you step through the procedure one line at a time? Or if you don’t quit after running the queries? If so, try adding a delay e.g. by using DoEvents before quitting

 

[DHookom]

The results in post #9 based on June’s query clearly showed there are both zero length strings and null values. That was why the Nz(field name, “”) filters worked.

@Duane
Just wondering why you thought the fields contained numbers. Possibly due to #9 which threw me at first

Yes, it was #9 which I wrongly assumed was the table datasheet rather than the datasheet of June7 query recommendation.

 

[MarkK]

Any reason the delete code dose not work When I shut down my database

You could do...

    Const Q_NAMES As String = _
        "OrphanAddCharges " & _
        "OrphanDaily " & _
        "OrphanAddChargesInter " & _
        "DeletedHorseDetails " & _
        "DeleteDupHorseID " & _
        "DeleteNoHorseID " & _
        "DeleteInvoice " & _
        "DeleteInvoiceNoNumber " & _
        "DeleteChargeNoAmount " & _
        "DeleteChargeNoID " & _
        "DeleteHorseNoName " & _
        "DeleteNoDetails"
        
    Dim var
    For Each var In Split(Q_NAMES)
        CurrentDb.Execute "qry" & var, dbFailOnError
    Next

DoCmd is not that sharp a tool.

 

[turniporange23]

I moved my code to OnLoad of my Main opening page instead of my closing button, and now the code is deleting the records thats have the 4 blank fields
Thanks for everybodys help
Bob