Loop through each column of an access table

[PokerRebel]

I am new to MS Access and was wondering how do I code the following using vba and looping each column in the table.

I have a table with 6 columns with about 500 records in the table. Col1 contains a date and time format. Col's 2 - 6 contain random "True" and "False" values in each column. I need to read col2, find the 1st "True" row (call it start time and date), lookup the date and time and add the start date and start time to a new table. it must then find the next "False" value in the same column (call it end time and date) and copy those values to the new table.

I must be able to do this for each of the columns. The new table must have the data added for each of the column names. ID, Col_Name, Start_Date, Start_Time, End_Date, End_Time.

I hope this makes sense. Please could someone assist me.

[ADezii]

Kindly provide some concrete Data, and the desired results that you are looking for. I'm a little hazy on the content of the question.

[PokerRebel]

Thanks for the speedy response.

I have a data logger that records events. Its creates a CSV file that i want to import into access to manipulate the data and write reports. When an event occurs it allocates "TRUE", with a date stamp (see txt attached for sample). Each column records a different event.

I need to know when the event records "True" (ie the date and time) and when the next "False" event occurs. (ie its date and time). In that column. So now i have a start date and time and an end date and time. One column could have 5 start and end dates and times, while another column could only have 2.

I thought it would be easier to create a new table and populate it with the extracted information and when be able to write a query to work out the time difference between the "True" and its next "False" event.

Does that help?

[ADezii]

The following code will process every Record in your Table (let's call it Table1) on a Column by Column basis recording the 1st occurrence of True in a Column, then the 1st occurrence of a False after the initial True is found. It should at least point you in the right direction. There is probably a less complex SQL solution that the SQL Gang will come up with, but therein lies my weakness (SQL).

Dim MyDB As DAO.database
Dim rst As DAO.Recordset
Dim intFldCtr As Integer
Dim blnFirstTrue As Boolean
Dim blnFirstFalseAfterTrue As Boolean
 
blnFirstTrue = False
blnFirstFalseAfterTrue = False
 
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("Table1", dbOpenSnapshot)
 
Debug.Print "------------------------------------------------"
 
With rst
  For intFldCtr = 1 To .Fields.Count - 1        'Skip the [TimeStamp] Field
    Do While Not .EOF
      If .Fields(intFldCtr) = True Then
        If Not blnFirstTrue Then        '1st true Value not found, but Field is True
          Debug.Print .Fields(0) & " | " & .Fields(intFldCtr).Name & " | " & .Fields(intFldCtr)
            blnFirstTrue = True         'Found 1st True, Reset
        End If       '1st True was found, now need the 1st False
      Else      'Field is False
        If Not blnFirstFalseAfterTrue And blnFirstTrue Then     '1st False not found, but 1st True was
          If .Fields(intFldCtr) = False Then  '1st True, then 1st False after True
            Debug.Print .Fields(0) & " | " & .Fields(intFldCtr).Name & " | " & .Fields(intFldCtr)
              blnFirstFalseAfterTrue = True
          End If
        End If
      End If
        .MoveNext
    Loop
      blnFirstTrue = False                  'Must Reset both Booleans for next Field
      blnFirstFalseAfterTrue = False
    Debug.Print "------------------------------------------------"
      .MoveFirst
  Next
End With
 
rst.Close
Set rst = Nothing

OUTPUT based on Sample.txt:

------------------------------------------------
24/02/2010 12:45 | Turbidity_>_20 | TRUE
24/02/2010 12:49 | Turbidity_>_20 | FALSE
------------------------------------------------
------------------------------------------------
24/02/2010 12:33 | Chlorine_SP_High | TRUE
24/02/2010 12:51 | Chlorine_SP_High | FALSE
------------------------------------------------
24/02/2010 12:50 | Flock_LL_Alarm | TRUE
24/02/2010 12:51 | Flock_LL_Alarm | FALSE
------------------------------------------------
24/02/2010 12:42 | Acoustic_LL_Alarm | TRUE
24/02/2010 12:46 | Acoustic_LL_Alarm | FALSE
------------------------------------------------

[PokerRebel]

How would you insert your output data in line 2 and line 3 into a table called "Data_rs", with these fields. EventID, Column_Name, Start_Date, Start_Time, End_Date, End_Time.

See actual data log file attached. I need to report on how long the event actually occured for during the day The event in each column in the sample could occur more than once and I need to total for the day.eg. the query works out the difference bewteen the start and end times and totals up the differences per day.

i am sorry to mess you around. Your code seems to work, but i need the output to go into a table. Thank you again for your assistance.

[PokerRebel]

I also have a probelm when the events totals per day, per column exceeds 24hours. My result when calculating the difference between the start date / time and the end date / time.

How do i over come that if i need tihe report to show 2 days 5 hours and 15 mins, or some format similar to that.

[ADezii]

Hopefully, the following code will point you in the right direction. I think what you are looking for is in Lines 38, 39, and 40.

Dim MyDB As DAO.database
Dim rst As DAO.Recordset
Dim intFldCtr As Integer
Dim blnFirstTrue As Boolean
Dim blnFirstFalseAfterTrue As Boolean
Dim varEventID As Variant
Dim varColumnName As Variant
Dim varStartDate As Variant
Dim varStartTime As Variant
Dim varEndDate As Variant
Dim varEndTime As Variant
 
blnFirstTrue = False
blnFirstFalseAfterTrue = False
 
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("Table1", dbOpenSnapshot)
 
Debug.Print "-------------------------------------------------------------------------------------"
 
With rst
  For intFldCtr = 2 To .Fields.Count - 1        'Skip the [EventID], and [TimeStamp] Fields
    Do While Not .EOF
      If .Fields(intFldCtr) = True Then
        If Not blnFirstTrue Then        '1st true Value not found, but Field is True
          varEventID = .Fields(0)
          varColumnName = .Fields(intFldCtr).Name
          varStartDate = Format(.Fields(1), "mm/dd/yyyy")
          varStartTime = Format(.Fields(1), "hh:mm AM/PM")
          'Debug.Print .Fields(0) & " | " & .Fields(intFldCtr).Name & " | " & .Fields(intFldCtr)
            blnFirstTrue = True         'Found 1st True, Reset
        End If       '1st True was found, now need the 1st False
      Else      'Field is False
        If Not blnFirstFalseAfterTrue And blnFirstTrue Then     '1st False not found, but 1st True was
          If .Fields(intFldCtr) = False Then  '1st True, then 1st False after True
            varEndDate = Format(.Fields(1), "mm/dd/yyyy")
            varEndTime = Format(.Fields(1), "hh:mm AM/PM")
            Debug.Print varEventID & " " & varColumnName & " " & " " & varStartDate & " " & _
                        varStartTime & " " & varEndDate & " " & varEndTime & " " & _
                        DateDiff("n", varStartTime, varEndTime) & " minutes"
            'Debug.Print .Fields(0) & " | " & .Fields(intFldCtr).Name & " | " & .Fields(intFldCtr)
              blnFirstFalseAfterTrue = True
          End If
        End If
      End If
        .MoveNext
    Loop
      varEventID = Null
      varColumnName = Null
      varStartDate = Null
      varStartTime = Null
      varEndDate = Null
      varEndTime = Null
        blnFirstTrue = False                  'Must Reset both Booleans for next Field
        blnFirstFalseAfterTrue = False
    Debug.Print "-------------------------------------------------------------------------------------"
      .MoveFirst
  Next
End With
 
rst.Close
Set rst = Nothing

OUTPUT:

-------------------------------------------------------------------------------------
10 Turbidity_>_20  02/24/2010 12:45 PM 02/24/2010 12:49 PM 4 minutes
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
7 Chlorine_SP_High  02/24/2010 12:33 PM 02/24/2010 12:51 PM 18 minutes
-------------------------------------------------------------------------------------
14 Flock_LL_Alarm  02/24/2010 12:50 PM 02/24/2010 12:51 PM 1 minutes
-------------------------------------------------------------------------------------
9 Acoustic_LL_Alarm  02/24/2010 12:42 PM 02/24/2010 12:46 PM 4 minutes
-------------------------------------------------------------------------------------

[PokerRebel]

The output file only loops the column once and creates the following results:
"2830 Turbidity_>_20 03/01/2010 08:39 AM 03/01/2010 08:39 AM 0 minutes", which is correct, but there are more ocurrences in that column. And those are the other records that are important. Not only just the 1st

eg it also occurs on
3342 | Turbidity_>_20 | 02/03/2010 00:01:26 | True
3343 | Turbidity_>_20 | 02/03/2010 00:01:30 | False

and i need that info as well

[PokerRebel]

Please can you help. The code works fine... i mod'd it a bit to write to a new table and also caluculate the time difference in a different format.

All i need the code to do is loop the entire columns and find ALL the "true" values and the next "false" values that occur in the column. At the moment it is only finding the first occurance.

[ADezii]

Didn't forget you, I have been extremely busy. I'll see if I can change the logic to accommodate 'ALL' the True then the next False Values using the DB and Data that you gave me.

[ADezii]

If my assumptions are correct, the logic now must be considerably more complex. Before I attempt to arrive at a solution, I must know that these assumptions are in fact correct. Based on the Data in Sample.txt that you provided in Post #3, are the following True/next False pairings correct fdor the Chlorine_SP_High Field?

Chlorine_SP_High
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
TRUE            Match Pair 1 True
TRUE            Match Pair 2 True
TRUE            Match Pair 3 True
TRUE            Match Pair 4 True
TRUE            Match Pair 5 True
TRUE
TRUE
TRUE
FALSE           Match Pair 1 False
FALSE           Match Pair 2 False
FALSE           Match Pair 3 False
FALSE           Match Pair 4 False
FALSE           Match Pair 5 False
 

[PokerRebel]

No! that is not right. Take a look at the actual data file in post #5. What happens is that the logger records occurances for the Turbidity_>_20, Chlorine_SP_High, etc. I need to record how long the occurance reocrds "True" for. By working each column individually, ie for Chlorine_SP_High. To work out the time difference, i find the first "True" occurrance in the column and subtract the next time a "False" occurrance occurs

Here is a manual example using the zipped .csv file in post #5.

Scanning colum Turbidity_>_20

01/03/2010 08:39 TRUE (Start)
01/03/2010 08:39 FALSE (End)

02/03/2010 00:01 TRUE (Start)
02/03/2010 00:01 FALSE (End)

02/03/2010 00:02 TRUE (Start)
02/03/2010 00:11 FALSE (End)

02/03/2010 00:13 TRUE (Start)
02/03/2010 00:13 FALSE (End)

02/03/2010 00:14 TRUE (Start)
02/03/2010 00:14 FALSE (End)

02/03/2010 00:15 TRUE (Start)
02/03/2010 00:17 FALSE (End)

02/03/2010 00:18 TRUE (Start)
02/03/2010 00:18 FALSE (End)

02/03/2010 00:20 TRUE (Start)
02/03/2010 03:03 FALSE (End)

02/03/2010 03:18 TRUE (Start)
02/03/2010 03:21 FALSE (End)

02/03/2010 03:27 TRUE (Start)
02/03/2010 03:36 FALSE (End)

02/03/2010 04:33 TRUE (Start)
02/03/2010 07:46 FALSE (End)

... until the end of the column.

Each column must be looped. Your vba code did it correctly, but only found the first "True" and the first next "False"occurence. Then it looped to the next column. it should have continued to scan the rest of the column and find the next "True" occurences and its next "False" occurence, so that i can subtract the start time (TRUE) from the end time (Flase).

I hope this makes more sense? Once again thanks for all the help...

[ADezii]

I do beleive that I fully comprehend the problem at hand and I'll see if I can come up with a viable solution. This is one of those problems, at least for me, that was very difficult to understand from the oposite end of a Web Page. I'll see if I can come up with something for you.

[ADezii]

I 'may' have good news for you, PokerRebel. Simply Download the Demo Database that I have been working on. It is Attached to this Post.

P.S. - I will not Post the final code until I absolutely know that it is working correctly. I'm also aware that there is a problem with Negative Time display (when spanning days), but that is not the Major Issue now, and can easily be fixed once I know that the overall Logic is correct.

[PokerRebel]

ADezii, you are a genius. That is absolutely correct. Thank you very much for all the hard work in making this possible.

[ADezii]

Not a problem, tomorrow or the next day, I'll see if I can get the Time Differentials to display correctly. How exactly would you like the Time Difference to appear in tblResults? Right now it is in Minutes in the Field [Diff (mins)].

[MrDeej]

Sub Gammel_Populer_Liste(SQL As String, Skjema As String, Liste As String)
Dim r As New ADODB.Recordset
Dim Coll As New Collection
Dim Tmpstr As String
'--------------------------
'Åpne recordset
'--------------------------
 
 
Set r = CurrentDb.OpenRecordset(SQL)
 
'r.Open SQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
 
'--------------------------
'Finn overskrifter
'--------------------------
For x = 1 To r.Fields.Count
    Coll.ADD (r.Fields(x - 1).Name)
Next x
 
'--------------------------
'Lag en string med overskriftene
'--------------------------
For x = 1 To Coll.Count
    MsgBox Coll(x)
    If Not Tmpstr = "" Then
        Tmpstr = Tmpstr & ";" & Coll(x)
    Else
        Tmpstr = Coll(x)
    End If
Next x
 
'--------------------------
'Endre kildetype på listen
'--------------------------
Forms(Skjema).Controls(Liste).RowSourceType = "Verdiliste"
 
'--------------------------
'Sett inn overskriftene i listen
'--------------------------
Forms(Skjema).Controls(Liste).RowSource = ""
Forms(Skjema).Controls(Liste).AddItem Tmpstr
 
'--------------------------
'Hent og sett inn verdiene
'--------------------------
 
'--------------------------
'Gå gjennom alle postene
'--------------------------
Do Until r.EOF
 
    '--------------------------
    'Tøm Coll
    '--------------------------
    For x = 1 To Coll.Count
        Coll.Remove (1)
    Next x
 
    '--------------------------
    'Hent verdier
    '--------------------------
    For x = 1 To r.Fields.Count
        Coll.ADD (r.Fields(x - 1).Value)
    Next x
 
    '--------------------------
    'Lag string
    '--------------------------
    Tmpstr = ""
    For x = 1 To Coll.Count
        If Not Tmpstr = "" Then
            Tmpstr = Tmpstr & ";" & Coll(x)
        Else
            Tmpstr = Coll(x)
        End If
    Next x
 
    '--------------------------
    'Sett inn post i listen
    '--------------------------
    Forms(Skjema).Controls(Liste).AddItem Tmpstr
 
    r.MoveNext
Loop
r.Close
 
 
Set rs = Nothing
Set Coll = Nothing
 
End Sub

Her is an code example from on of my employees, maybe this also is a solution?

[PokerRebel]

Hey ADezii. It does not matter what format you give the time difference to me in. I can always change it myself.

[ADezii]

I have made the following changes/enhancements to the Project and will Upload the Revised Database sometime this afternoon:

1. Eliminated, for the most part, the display of Negative Values for the Time Differential by revising the manner in which it is calculated.
2. In addition to the Standard Differential Display in Minutes as a Whole number, added a more intuitive Display as an additional Column, such as:
   1 Day(s) - 4 Hour(s) - 37 Minute(s), No Differential, Undetermined, etc.
3. Cleaned up the code a little.
4. Created a simple Query that will display erroneous Results such as when an End Date is earlier than the Start Date (Negative Values).

P.S. - I'm not sure as to whether you are aware of it or not, but there is a problem with the Data as it exists in Digital.csv. The Date Range progresses accordingly in Digital.csv: 2/24/2010 thru 2/28/2010, 1/3/2010, then 2/3/2010. If the last entry ([TimeStamp]) for 2/28/2010 happens to be True and at least the first entry for 1/3/2010 is True, herein lies the problem. The Logic, rightfully so, interprets this as a legitimate True Range, then finds the Next False Value. When this happens, the Differential is calculated, but unfortunately the End Date is earlier than the Start Date, hence erroneous results (Negative Values). I'll probably return an Error! Value for these Records, then you do whatever you like with them. There are also a couple of Records where the End Date is in fact greater than the Start Date, but the difference is much greater than normally anticipated. Just wasn't sure if you were aware of this or not. Will return with the Revised DB later on.

[PokerRebel]

I don't know how much to thank you, you have been a tremendous help.

I am aware that there would be lost data as such because the digital.csv file gets brought back dauily and the table gets updates. So! there might be data or occurances that have not completed as such. If you understand what i am saying. I will only run the report for those days that i have completed data for and also, they must understand that the data recieved could be incomplete as some time differences actually run over 24 hours.

THANK YOU! for what u have done...

[ADezii]

You are quite welcome. Ironically, the problem was not so much in finding the Solution, as it was understanding the Problem! (LOL). Download the Attachment to View what I consider to be the 'Final Version' of this Project. It can probably be improved somewhat, but right now It Ain't Broke so Don't Fix It! (LOL). If you have time, get back to me on this Attachment to see what you think and to make sure that it is fully operational. Take care.