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)>=200seconds or (false7-false5)>=200
or (false8 - false5)>= 200.
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)>=200seconds.
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 implement this that will be great.
Thanks in advance.
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)>=200seconds or (false7-false5)>=200
or (false8 - false5)>= 200.
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)>=200seconds.
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 implement this that will be great.
Thanks in advance.
