434,985 Members | 2,884 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

# VBA Access date time

 P: 4 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. Oct 24 '06 #1
6 Replies

 Expert Mod 10K+ P: 14,534 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 Expand|Select|Wrap|Line Numbers   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     Oct 25 '06 #2

 P: 4 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 Expand|Select|Wrap|Line Numbers   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 Oct 25 '06 #3

 Expert Mod 15k+ P: 31,492 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. Oct 25 '06 #4

 Expert Mod 10K+ P: 14,534 Ok should all work now: Expand|Select|Wrap|Line Numbers   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     Oct 25 '06 #5

 P: 4 Ok should all work now: Expand|Select|Wrap|Line Numbers   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. Oct 31 '06 #6

 Expert Mod 15k+ P: 31,492 Which parameters does it ask for? Oct 31 '06 #7