Find if there is any blank values in a query field

[boboivan]

Hello!



When I open my MainMenu form, I'd like to have a warning massage if there is any null values in a specific query (Query1).
I've tried so far the following code in my On Load Event of the MainMenu form, but there is something missing.

Private Sub Form_Load()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Query1", dbOpenSnapshot, dbReadOnly)
rs.FindFirst IsNull("FieldNameToFindNullValue")
If True Then
Me.Warning.Visible = True
Me.Warning.Value = "Empty values!"
Else
Me.Warning.Visible = False
End If
End Sub

What am I doing wrong?

Thx!

[boboivan]

This code works fine if it finds any null value:

Private Sub Form_Load()
Dim seepp As String
seepp = DLookup("AnotherFieldOfTheQuery", "Query1", "IsNull([FieldNameToFindNullValue])")    
If seepp = True Then ...

But if it doesn't find any null values, I get Run-time error '94': "Invalid use of Null"

Any ideas?

[orange]

I wrote this routine to demo how the looping construct with recordset you provided in post1 could b modified to do what you are asking. Namely, to review a query and see if any of the fields were NULL or were zero length strings.

'---------------------------------------------------------------------------------------
' Procedure : boboivan
' Author    : mellon
' Date      : 21/02/2016
' Purpose   : To see if there are any null or ZLS(zero length strings) in any fields in a query
'
' If any fields are NULL or ZLS, then increment variable emptyFieldCount
'---------------------------------------------------------------------------------------
'
Sub boboivan()
          Dim sql As String   'the query sql
10       On Error GoTo boboivan_Error

20        sql = "SELECT ancestor.name, ancestor.id, ancestor.deathdate, ancestor.birthdate, ancestor.preregcode " _
                & " FROM ancestor;"
          Dim db As DAO.Database
          Dim rs As DAO.Recordset
          Dim emptyFieldCount As Integer
30        emptyFieldCount = 0
          Dim i As Integer
40        Set db = CurrentDb
50        Set rs = db.OpenRecordset(sql, dbReadOnly)
60        With rs
70            Do While Not .EOF
80                If IsNull(![name]) Or Len(![name] & "") = 0 Then emptyFieldCount = emptyFieldCount + 1
90                If IsNull(![id]) Or Len(![id] & "") = 0 Then emptyFieldCount = emptyFieldCount + 1
100               If IsNull(![deathdate]) Or Len(![deathdate] & "") = 0 Then emptyFieldCount = emptyFieldCount + 1
110               If IsNull(![birthdate]) Or Len(![birthdate] & "") = 0 Then emptyFieldCount = emptyFieldCount + 1
120               If IsNull(![preregcode]) Or Len(![preregcode] & "") = 0 Then emptyFieldCount = emptyFieldCount + 1
130           rs.MoveNext
140           Loop
150       End With
160       Debug.Print IIf(emptyFieldCount > 0, "EmptyFieldsFound", "No EmptyFields")

170      On Error GoTo 0
180      Exit Sub

boboivan_Error:

190       MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure boboivan of Module AccessD_KillText"

End Sub

My table ancestor

name
id
deathdate
birthdate
preregcode

sam


12/03/1976
01/11/1922
21

bob
1
13/03/1976
31/10/1922
44

jim
2
17/09/1964
22/03/1944
63

ken
45
07/02/2006
09/09/1999
82

TestPerson
169
19/03/2009
21/07/1937



TestPerson
170
19/03/2009
21/07/1937



TestPerson
171
19/03/2009
21/07/1937



TestPerson172
172
19/03/2009
21/07/1937



TestPerson173
173
19/03/2009
21/07/1937



TestPerson174
174
19/03/2009
21/07/1937



TestPerson175
175
19/03/2009
21/07/1937



TestPerson176
176
19/03/2009
21/07/1937

Result of the routine:

EmptyFieldsFound

Good luck.

[boboivan]

Thank you Orange! You're a gent!

I've tried to run the macro in the Event On Load form, with an if condition like:

Private Sub Form_Load()
If boboivan True Then 
do stuff
End if
End Sub

and I get a Compile error: Expected variable or procedure, not module".

On the other hand, I really think that the second code is more useful to me, as can return also in my warning msgbox the value in "AnotherFieldOfTheQuery" associated with the null value in "FieldNameToFindTheNulValue".
How can I fix that Run-time error '94': "Invalid use of Null"?

Thx a lot again!

[boboivan]

Got it!

The working code is:

Private Sub Form_Load()
Dim seepp As String
Dim countpp As String


countpp = DCount("AnotherFieldOfTheQuery", "Query1", "IsNull([fieldNameToFindNullValue])")
    
If countpp <> 0 Then
seepp = DLookup("AnotherFieldOfTheQuery", "Query1", "IsNull([fieldNameToFindNullValue])")
do other stuff
End If

End Sub