Speed up my VBA access Code

[TJPoorman]

Use the NZ() function around the rs field. Like this:

Count1=NZ(rs![1],0)

 

[mfaqueiroz]

Thanks for your help !
it is really usefull

I've also other function that is really slow :/ , maybe you have some tips to improve that.
I have one table with all the events(arround 915755 registers) and other with the Maintenance Events(9000).
If one off my "on" or "off" events occured 15 seconds before or after the Maintenance Events , they are flagged as "Maintenance".




Sub Maintenance()


Dim TempoEvenLog As Date
Dim TempoMaintenance
Dim Dif As Long
Dim MachineCode as String
Dim ID as integer
Dim Status as String
Dim Dif as Long
Dim TagMan as string
Dim MachineCodeMan as strinG
dIM Machine AS String



Set dbs = DBEngine(0)(0)
Set Maintenance = dbs.OpenRecordset("TabMaintenance", DB_OPEN_TABLE) 'Maintenance
Set Events = dbs.OpenRecordset("DesLig", DB_OPEN_DYNASET)
Set Results = dbs.OpenRecordset("Results", DB_OPEN_DYNASET)


Events.MoveFirst

Do While Not Events.EOF
If IsNull(Events.Fields(15)) Then
DateTime = Events.Fields(2) 'RTU date
Machine = Events.Fields(4)
MachineCode = Events.Fields(5)
ID = Events.Fields(0)
Tag = Left(Events.Fields(8), 10)
Status = Events.Fields(7)

Maintenance.MoveFirst
Do While Not ELog.EOF
MachineMan = ELog.Fields(5)
TimeMAN = ELog.Fields(1) 'RTU date
MachineCodeMan = ELog.Fields(6)
IDman = ELog.Fields(0)
Dif = DateDiff("s", TimeMAN, DateTime)
TagMAN = Left(ELog.Fields(4), 10)

If Status = "Off" And Tag = TagMAN And Dif >= -15 And Dif <= 15 Then

Results.AddNew
Results.Fields(1) = ID
Results.Fields(2) = "Maintenance"
Results.Fields(4) = MachineCode
Results.Fields(5) = Machine
Results.Fields(6) = DateTime
Results.Fields(16) = "OFF"
Results.Update

Exit Do


ElseIf Status = "On" And Tag = TagMAN And Dif >= -15 And Dif <= 15 Then
Results.AddNew
Results.Fields(1) = ID
Results.Fields(2) = "Maintenance"
Results.Fields(4) = MachineCode
Results.Fields(5) = Machine
Results.Fields(6) = DateTime
Results.Fields(16) = "ON"
Results.Update


Exit Do


Else

End If
Maintenance.MoveNext

Loop
End If
Events.MoveNext
Loop

End Sub