VBA Access date time

[gre1unix]

I am new to VBA Access. I create "mytable" with 2 columns from existing main table by giving start date and end date.
Given below is mytable.
First column: dt( date hr:min:sec format )
Second column: string( the row elements could be true or false or none )
dt string
3/4/04 1:05:30 PM true
3/4/04 1:06:39 PM false
3/5/04 1:06:42 PM false
3/5/04 1:07:40 PM none
3/5/04 1:08:00 AM false
3/5/04 1:08:10 PM false
3/5/04 1:24:20 PM false
3/5/04 1:34:20 PM false
3/6/04 1:36:30 PM true
In the string column, the VBA code should look for "false". Look for a pattern where false is in continuous order like ( false2, false3 )
or (false5, false6, false7, false8) and increment the counter if the time difference (false3 - false2)>=180seconds or (false7-false5)>=180
or (false8 - false5)>= 180.
The output should give final value of counter and p = (counter/num)*100
In the above case, the output will be counter=3 because of (false6 - false5) ( false7 - false5 ) and (false8 - false5) and value of
p=(3/num)*100
num = datediff(false8 - false5)
Note: false2 means 2nd element in string column is false.If it would have been false5, false6 and false7 and then true8 then the counter should increment if (false7-false5)>=180seconds.

Output should be

Date counter p
3/4/04 0 0
3/5/04 3 (3/num)*100
3/6/04 0 0
If someone can give the VBA code that will be great.
Thanks in advance.

[MMcCarthy]

OK try this. I'm not sure if I've caught all the logic but we'll see.

In the VB Editor window go to tools - references on the menu bar and make sure there is a Microsoft DAO library ticked.

In mytable change the field name string to myStr

Create a new table called myNewTable with fields; newDate, counter, p

 
Function falseStats()
Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim tempDate As Date
Dim NoOfDays As Integer
Dim tempValue As String
Dim NoOfSeconds As Integer
Dim counter As Integer
Dim result As Boolean
 
 strSQL="SELECT * FROM mytable ORDER BY dt;"
 
 Set db = CurrentDb
 Set rs = db.OpenRecordset(strSQL)
 rs.MoveFirst
 Do until rs.EOF
  result = True
  tempDate = rs!dt,
  tempValue = rs!myStr
  NoOfSeconds=0
  counter=0
  If tempValue = "false" Then 'first record false
   do until result=False   'repeat while false in value
    rs.MoveNext
    If rs!myStr = "false" Then 'next record is false
     NoOfSeconds=DateDiff ('s', tempDate, rs!dt)
     NoOfDays=DateDiff('d', tempDate, rs!dt)
     If NoOfSecond >=180 Then
      counter=counter+1
     End If
    Else
     rs.MovePrev
     DoCmd.RunSQL ("INSERT INTO myNewTable (newDate, Counter, p) " & _
                   "VALUES (Format(rs!dt,"Short Date"), counter, (counter/NoOfDays)*100);")
     result=False
    End If
   loop
  Else
   DoCmd.RunSQL ("INSERT INTO myNewTable (newDate, Counter, p) VALUES (Format(rs!dt,"Short Date"), 0, 0);")
  End If
  rs.MoveNext
 Loop
 
 rs.Close
 Set rs = Nothing
 Set db = Nothing
 
End Function
 
 

[gre1unix]

OK try this. I'm not sure if I've caught all the logic but we'll see.

In the VB Editor window go to tools - references on the menu bar and make sure there is a Microsoft DAO library ticked.

In mytable change the field name string to myStr

Create a new table called myNewTable with fields; newDate, counter, p

 
Function falseStats()
Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim tempDate As Date
Dim NoOfDays As Integer
Dim tempValue As String
Dim NoOfSeconds As Integer
Dim counter As Integer
Dim result As Boolean
 
 strSQL="SELECT * FROM mytable ORDER BY dt;"
 
 Set db = CurrentDb
 Set rs = db.OpenRecordset(strSQL)
 rs.MoveFirst
 Do until rs.EOF
  result = True
  tempDate = rs!dt,
  tempValue = rs!myStr
  NoOfSeconds=0
  counter=0
  If tempValue = "false" Then 'first record false
   do until result=False   'repeat while false in value
    rs.MoveNext
    If rs!myStr = "false" Then 'next record is false
     NoOfSeconds=DateDiff ('s', tempDate, rs!dt)
     NoOfDays=DateDiff('d', tempDate, rs!dt)
     If NoOfSecond >=180 Then
      counter=counter+1
     End If
    Else
     rs.MovePrev
     DoCmd.RunSQL ("INSERT INTO myNewTable (newDate, Counter, p) " & _
                   "VALUES (Format(rs!dt,"Short Date"), counter, (counter/NoOfDays)*100);")
     result=False
    End If
   loop
  Else
   DoCmd.RunSQL ("INSERT INTO myNewTable (newDate, Counter, p) VALUES (Format(rs!dt,"Short Date"), 0, 0);")
  End If
  rs.MoveNext
 Loop
 
 rs.Close
 Set rs = Nothing
 Set db = Nothing
 
End Function
 
 

Statements given below give errors.
tempDate = rs!dt, ( error because of comma )

result=False ( Error is False not defined)

NoOfSeconds=DateDiff ('s', tempDate, rs!dt) '( This is in red )

NoOfDays=DateDiff('d', tempDate, rs!dt) '( This is in red )


DoCmd.RunSQL ("INSERT INTO myNewTable (newDate, Counter, p) " & _
"VALUES (Format(rs!dt,"Short Date"), counter, (counter/NoOfDays)*100);") '( This is in red )


DoCmd.RunSQL ("INSERT INTO myNewTable (newDate, Counter, p) VALUES (Format(rs!dt,"Short Date"), 0, 0);") '( This is in red )

Please advise.
Thanks

[NeoPa]

False not defined implies you have some library missing.
In the VBA debugger go to Tools / References... and see which are ticked
I thought False was a built-in constant so should be in the 'Visual Basic for Applications' library.

The others all seem to be ok (to me).
If they show as red in the debugger, try deleting them and re-entering them and note the error message. That might give a clue.
If it says the functions are not defined then that again is a library issue.

[MMcCarthy]

Ok should all work now:

 
Function falseStats()
Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim tempDate As Date
Dim NoOfDays As Integer
Dim tempValue As String
Dim NoOfSeconds As Integer
Dim counter As Integer
Dim rslt As Boolean
 
    strSQL = "SELECT * FROM mytable ORDER BY dt;"
 
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    rs.MoveFirst
    Do Until rs.EOF
        rslt = True
        tempDate = rs!dt
        tempValue = rs!myStr
        NoOfSeconds = 0
        counter = 0
        If tempValue = "false" Then 'first record false
        Do Until rslt = False 'repeat while false in value
            rs.MoveNext
            If rs!myStr = "false" Then 'next record is false
                NoOfSeconds = DateDiff("s", tempDate, rs!dt)
                NoOfDays = DateDiff("d", tempDate, rs!dt)
                If NoOfSeconds >= 180 Then
                    counter = counter + 1
                End If
            Else
                rs.MovePrevious
                DoCmd.RunSQL ("INSERT INTO myNewTable (newDate, Counter, p) " & _
                          "VALUES (Format(rs!dt,'Short Date'), counter, (counter/NoOfDays)*100);")
                rslt = False
            End If
        Loop
    Else
        DoCmd.RunSQL ("INSERT INTO myNewTable (newDate, Counter, p) VALUES (Format(rs!dt,'Short Date'), 0, 0);")
    End If
    rs.MoveNext
    Loop
 
 rs.Close
 Set rs = Nothing
 Set db = Nothing
 
End Function
 
 

[gre1unix]

Ok should all work now:

 
Function falseStats()
Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim tempDate As Date
Dim NoOfDays As Integer
Dim tempValue As String
Dim NoOfSeconds As Integer
Dim counter As Integer
Dim rslt As Boolean
 
    strSQL = "SELECT * FROM mytable ORDER BY dt;"
 
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    rs.MoveFirst
    Do Until rs.EOF
        rslt = True
        tempDate = rs!dt
        tempValue = rs!myStr
        NoOfSeconds = 0
        counter = 0
        If tempValue = "false" Then 'first record false
        Do Until rslt = False 'repeat while false in value
            rs.MoveNext
            If rs!myStr = "false" Then 'next record is false
                NoOfSeconds = DateDiff("s", tempDate, rs!dt)
                NoOfDays = DateDiff("d", tempDate, rs!dt)
                If NoOfSeconds >= 180 Then
                    counter = counter + 1
                End If
            Else
                rs.MovePrevious
                DoCmd.RunSQL ("INSERT INTO myNewTable (newDate, Counter, p) " & _
                          "VALUES (Format(rs!dt,'Short Date'), counter, (counter/NoOfDays)*100);")
                rslt = False
            End If
        Loop
    Else
        DoCmd.RunSQL ("INSERT INTO myNewTable (newDate, Counter, p) VALUES (Format(rs!dt,'Short Date'), 0, 0);")
    End If
    rs.MoveNext
    Loop
 
 rs.Close
 Set rs = Nothing
 Set db = Nothing
 
End Function
 
 

I apologize for the late reply. But when I run the code, it asks for parameters( I dont want that way ). Please advise.

[NeoPa]

Which parameters does it ask for?