I've used VBA in other Office applications.
I'm tracking entry and exit to my project with a magnetic card and
saving the data in Access.
The hardware which runs the system adds anywhere between 1-4 records
(each about 10 milliseconds appart) each time a person swipes their
card.
I need someone's help on how to use VBA to iterate through a column,
compare the times of entry, and delete the duplicate rows.
I created a report, which shows the min time and max time, split up
across a 24 hour period. I exported that to Excel and then wrote VBA
code to make sure that max time > min time + delta.
delta is the number of seconds in which a person can re-scan and not
have it counted as the next part of the time-card cycle (clock in or
clock out)
Sub CompareTime()
'
' This function will total the number of people who scaned in and out
' and also the number that scanned in, but did not scan out,
dicounting an 11 minute window
' where multiple scans can occur.
' CompareTime Macro
' Macro V.1 created 12/21/2007 by Mttmwsn
'
'Before you use this application, you must update the name of the
Worksheet and the Range
Dim ScanInAndOutCount As Integer
Dim ScanInOnlyCount As Integer
ScanInAndOutCount = 0
ScanInOnlyCount = 0
For Each c In Worksheets("Date Range Query").Range("C2
7390")
c.Activate
If c.Value < c.Offset(RowOffset:=0, ColumnOffset:=1).Value - 0.008
And c.Offset(RowOffset:=0, ColumnOffset:=1) > 0 Then
ScanInAndOutCount = ScanInAndOutCount + 1
End If
Next c
For Each c In Worksheets("Date Range Query").Range("C2
7390")
c.Activate
If c.Value >= c.Offset(RowOffset:=0, ColumnOffset:=1).Value -
0.008 And c.Offset(RowOffset:=0, ColumnOffset:=1) > 0 Then
ScanInOnlyCount = ScanInOnlyCount + 1
End If
Next c
MsgBox (ScanInOnlyCount + ScanInAndOutCount & " people scanned in. " &
ScanInAndOutCount & " scanned in and out. " & ScanInOnlyCount & "
scanned in, but did not scan out.")
'
End Sub
