Dcount->Run-time error '94'. Invalid use of Null.

[mfaqueiroz]

update: SOLVED!
empty row caused the error

Hi,
I use the DCount command to count the number of "Invalids" or "Stop" , 40 miliseconds after one "Off" in different machines but with the same function.

It is appearing this error, but i can't understand the reason

Run-time error '94'. Invalid use of Null.

Following there is the part of code that have the information relative to my issue.

Set dbs = DBEngine(0)(0)
Set OFF = dbs.OpenRecordset("TABOFF", DB_OPEN_DYNASET) 
    

    OFF.MoveFirst
    Do While Not OFF.EOF
        
        Machine = OFF.Fields(17)
        TimeMSOFF = OFF.Fields(2)
        Status = OFF.Fields(5)
        MachineFunction = OFF.Fields(3)
        
 
TimeMS40 = TimeMSOFF + 40 


CountERRORS = Nz(DCount("*", "TabInput", " ((TabInput.[TimeMS]) <= " & TimeMS40 & ")  and  ((TabInput.[TimeMS])>= " & TimeMSOFF & ") And (TabInput.[Status] = 'INVALID' or TabInput.[Status] = 'STOP') AND TabInput.[MACHINECODe] = 'X1S' AND TabInput.[Machine] <> '" & Machine & "' AND TabInput.[MachineFunction] = '" & MachineFunction & "' "), 0)

I really appreciate your help!

 

[mfaqueiroz]

Re: Dcount->Run-time error '94'. Invalid use of Null [SOLVED]

It is solved!
Was a empty row that caused the problem.

 

[Minty]

Add a
debug.print Machine; TimeMSOFF; Status; MachineFunction

to your loop and see the values being returned in your query. Also I think Status may be a reserved word so try changing it to something else.

 

[cnstarz]

It might be worthwhile to look into creating a query instead and use Recordset.Recordcount or Count(*) instead of Dcount. Domain aggregate functions ignore null values and are generally slower than queries.