deleting records with null values

[markjkubicki]

if rather than deleting the full record, the end user merely wipes out the entry, I'd like to delete the record with code (if that's what they really wanted to do)

as an example, the table has (4) records and the user clears one;
i then run the following code in the AfterUpdate() event
however, the results are:
tl count 4
isNull 0
not isNull 4
after deletion, tl count 4

effectively: nothing happens: it does not see any null records, none are deleted

I'm stuck and any suggestions/directions will be greatly appreciated in advance,
m.

If gsMsgResponse = vbYes Then
            
                DoCmd.SetWarnings False
                Debug.Print "tl count: " & DCount("[Prgrm_ID]", "tbeIncentive_Prgrms")
                Debug.Print "isnull: " & DCount("[Prgrm_ID]", "tbeIncentive_Prgrms", "IsNull([Prgrm_Descript])")
                Debug.Print "not isnull: " & DCount("[Prgrm_ID]", "tbeIncentive_Prgrms", "Not IsNull([Prgrm_Descript])")
                
                gsSQL = "Delete * from tbeIncentive_Prgrms" & _
                    " WHERE isnull([Prgrm_Descript]);"
                CurrentDb.Execute gsSQL, dbFailOnError

                Debug.Print "after deletion, tl count: " & DCount("[Prgrm_ID]", "tbeIncentive_Prgrms", IsNull([Prgrm_Descript]))
            
            
            Else
                Me.cboPrg.Undo
            End If

            '....

[Gicu]

Can you try it like this:

DoCmd.SetWarnings False
Debug.Print "tl count: " & DCount("[Prgrm_ID]", "tbeIncentive_Prgrms")
Debug.Print "isnull: " & DCount("*", "tbeIncentive_Prgrms", "[Prgrm_Descript] Is Null")
Debug.Print "not isnull: " & DCount("*", "tbeIncentive_Prgrms", "[Prgrm_Descript] Is Not Null")
                
gsSQL = "Delete * from tbeIncentive_Prgrms" & _
		" WHERE [Prgrm_Descript] Is Null;"
CurrentDb.Execute gsSQL, dbFailOnError

Cheers,

[markjkubicki]

wow... that simple (?!) thnx

... Is Null vs. IsNull()

for anyone else with a lesson to be learned... this article is pretty decent:
https://www.techrepublic.com/article...ull-in-access/

[CJ_London]

the results are:
tl count 4
isNull 0
not isNull 4
after deletion, tl count 4

your result shows you don’t have a record with a null value

you can use the isnull function or is null sql, neither will delete anything since there isn’t a null value to be identified.

it may be your value is text and what you actually have is a zls (zero length string) so you need another method which I suggest would be

where nz([Prgrm_Descript],””)=“”



I

[moke123]

the end user merely wipes out the entry,

Has the record been saved before the code is run?