A Time Problem

[gvirden]

I am collecting data from 12 noon on one day to 12 noon on another day and writing to the database. I need a new record for each cycle.

For example, on any given day, a new record would be written on any data past 12 noon, and as new information is gathered it will be written on this current record up until 12 noon the next day. Then the cycle will repeat itself. Any data prior to 12 noon that day up until 12 noon the previous day would be on the previous record.

I'm assuming this query would be written in the SQL WHERE statement.

The first thing I need to do is check the systems date to see if it is before noon or after noon. If it is before noon it needs to be written to that record (if one exists,if not it needs to start a new one) and vise-a-versa for after noon.

How would you write the SQL WHERE statement?

[gvirden]

Should I change my angle on this problem? I couldn't help but notice that three of the best VB programming minds in this forum have responded since I posted my question-- that's JHausmann, Serge, and smalig (in no particular order). If I'm out in left field tell me!!!

Also I've noticed that smailg has open a forum at his site. Is this open to these kinds of questions?

[JHausmann]

I didn't respond because I'm not entirely clear what you want. If possible, give the table structure, too. Thanks.

[gvirden]

Thanks for the reply.I solved my time problem with an if statement (see code, I don't know if this is the best way but it works).

But I'm stuck on something else. I have several ponds with identical equipment in them. This equipment is turned on during the night. I have a control array on the form containing this equipment. As employees go from pond to pond turning on equipment (and checking the control array boxes) the app writes to the recordset the time each piece was turned on. Additionally, it writes to the chkboxes on the form the caption = time, it sets enable to false, and it colors the background red. I do this to get a time record from each employee. Once the chkbox has been checked and disabled its locked until another recordset can be added at 12 noon and the whole process starts over.

My problem is this. I go from pond to pond using a combo list. When I come back to a pond that has equipment on (and it has been updated in the recordset) I want the control array to get the data from the recordset and set the appropriate properties. Below is where I am so far. I used Elseif to deal with null values in the recordset. The application runs fine other than not setting any properties in my control array. Any suggestions?

Private Sub getSetSwitch()

    Dim sSQL As String
    Dim dTime
    Dim today As Date
    Dim i As Integer
    
    dTime = Format(Time, "Short Time")
    today = Date

    If Date = today And (dTime > #12:00:00 PM# Or dTime = #12:00:00 PM#) Then
                
        sSQL = "SELECT * FROM Equipment " & _
        "WHERE (((Equipment.Pond)= '" & cmbpond.Text & "') AND ((Equipment.theDate)=#" & today & "#))"
    
        Set rsEquipment = GetRecordSet(sSQL)
            
        Else:
        
        sSQL = "SELECT * FROM Equipment " & _
        "WHERE (((Equipment.Pond)= '" & cmbpond.Text & "') AND ((Equipment.theDate)=#" & today - 1 & "#))"
    
        Set rsEquipment = GetRecordSet(sSQL)
           
    End If
    
    If rsEquipment.EOF = True And rsEquipment.BOF = True Then
        
        Exit Sub
        
    End If
    
    With rsEquipment
    
        If .Fields("A1") <> Null Then
            chkSwitch(0) = .Fields("A1")
            
        ElseIf .Fields("A2") <> Null Then
            chkSwitch(1) = .Fields("A2")
            
        ElseIf .Fields("A3") <> Null Then
            chkSwitch(2) = .Fields("A3")
            
        ElseIf .Fields("A4") <> Null Then
            chkSwitch(3) = .Fields("A4")
            
        ElseIf .Fields("T1") <> Null Then
            chkSwitch(4) = .Fields("T1")
            
        ElseIf .Fields("T2") <> Null Then
            chkSwitch(5) = .Fields("T2")
            
        ElseIf .Fields("T3") <> Null Then
            chkSwitch(6) = .Fields("T3")
            
        ElseIf .Fields("T4") <> Null Then
            chkSwitch(7) = .Fields("T4")
            
        ElseIf .Fields("T5") <> Null Then
            chkSwitch(8) = .Fields("T5")
            
        ElseIf .Fields("T6") <> Null Then
            chkSwitch(9) = .Fields("T6")
            
        ElseIf .Fields("T7") <> Null Then
            chkSwitch(10) = .Fields("T7")
            
        ElseIf .Fields("T8") <> Null Then
            chkSwitch(11) = .Fields("T8")
            
        ElseIf .Fields("D1") <> Null Then
            chkSwitch(12) = .Fields("D1")
            
        ElseIf .Fields("D2") <> Null Then
            chkSwitch(13) = .Fields("D2")
            
        ElseIf .Fields("D3") <> Null Then
            chkSwitch(14) = .Fields("D3")

        ElseIf .Fields("D4") <> Null Then
            chkSwitch(15) = .Fields("D4")
            
        End If
    
    End With
    
        For i = 0 To CS_D4
        
            If chkSwitch(i) <> Null Then
            
                With chkSwitch(i)
                    .Caption = chkSwitch(i)
                    .Enabled = False
                    .BackColor = &HFF&
                    
                End With
                
            End If
            
        Next i
    
End Sub

[JHausmann]

Try the IsNull function instead.


If NOT IsNull(.Fields("A1")) Then chkSwitch(0) = .Fields("A1")

[gvirden]

OK that works good.

But it fires the index and writes a new current time in the rsEquipment.fields( "whatever field is not null"). Below is part of the code.

Private Sub chkSwitch_Click(index As Integer)

Dim dTime

dTime = Format(Time, "hh:mm")

getTodayandYesterday 'get the right record

With rsEquipment

Select Case index

Case CS_A1
chkSwitch(0).Caption = dTime
chkSwitch(0).Enabled = False
chkSwitch(0).BackColor = &HFF&
.Fields("A1") = Format(Time, "short time")
.Update

Case CS_A2............bla blah

How can I keep it from changing the record?

[JHausmann]

Changing what record ?

[gvirden]

I fixed it and you were very helpful, thanks.