Logical Error in Time

[reycons]

Dim li As ListItem
Dim LV As ListView
Dim rs As Recordset
Dim rsfullLogs As Recordset
Dim cn As New Connection
Dim dlogindate, dLogOut, t_AmTimeIn, t_AmTimeOut, t_PMTimeIn, t_PmTimeOut, t_AmtimeIn2, t_AmTimeOut2, t_PmTimeOut2, t_PmTimeIn2 As Date

Set rs = New Recordset
Set rsfullLogs = New Recordset
Set rsviewnologout = New ADODB.Recordset
Set cn = New Connection

cn.CursorLocation = adUseClient

cn.connectionstring = "Provider=MSDASQL.1;" & "Persist Security Info = False;" & "Data Source=Timekeeper;"
cn.Open

rs.Open "SELECT Name,Logindate,LogoutDate,AMTimeIn,AMTimeOut,PMTimeIn,PMTimeOut,AmTimeIn2,AmTimeOut2,PmTimeIn2,PMTimeOut2,Remarks FROM DTR INNER JOIN Employee ON DTR.Emppk Like Employee.pk WHERE LoginDate >= cdate('" & DTPicker1.Value & "') AND LoginDate <= cdate('" & DTPicker2.Value & "') and Name Like '" & Me.cmbFullname.Text & "'", ConnTimeKeeper

Set LV = lvlogs
LV.ListItems.Clear

If rs.RecordCount <> 0 Then

Do While Not rs.EOF
Set li = LV.ListItems.Add(, , rs("Name") & "")
li.ListSubItems.Add , , rs("logindate") & ""
li.ListSubItems.Add , , rs("AmTimeIN") & ""
li.ListSubItems.Add , , rs("AmTimeOut") & ""
li.ListSubItems.Add , , rs("PMTimeIN") & ""
li.ListSubItems.Add , , rs("PMTimeOut") & ""
li.ListSubItems.Add , , rs("AmTimeIn2") & ""
li.ListSubItems.Add , , rs("AmTimeOut2") & ""
li.ListSubItems.Add , , rs("PmTimeIn2") & ""
li.ListSubItems.Add , , rs("PmTimeOut2") & ""
li.ListSubItems.Add , , rs("LogoutDate") & ""
li.ListSubItems.Add , , rs("Remarks") & ""

cName = IIf(Not IsNull(rs.Fields!Name), rs.Fields!Name, "")
dlogindate = IIf(Not IsNull(rs.Fields!LoginDAte), rs.Fields!LoginDAte, Empty)
dLogOutDate = IIf(Not IsNull(rs.Fields!LogoutDate), rs.Fields!LogoutDate, Empty)
t_AmTimeIn = IIf(Not IsNull(rs.Fields!AMTimeIn), rs.Fields!AMTimeIn, Empty)
t_AmTimeOut = IIf(Not IsNull(rs.Fields!AMTimeOut), rs.Fields!AMTimeOut, Empty)
t_PMTimeIn = IIf(Not IsNull(rs.Fields!PMTimeIn), rs.Fields!PMTimeIn, Empty)
t_PmTimeOut = IIf(Not IsNull(rs.Fields!PMTimeOut), rs.Fields!PMTimeOut, Empty)
t_AmtimeIn2 = IIf(Not IsNull(rs.Fields!AmTimeIn2), rs.Fields!AmTimeIn2, Empty)
t_AmTimeOut2 = IIf(Not IsNull(rs.Fields!AmTimeOut2), rs.Fields!AmTimeOut2, Empty)
t_PmTimeIn2 = IIf(Not IsNull(rs.Fields!PMTimeIn2), rs.Fields!PMTimeIn2, Empty)
t_PmTimeOut2 = IIf(Not IsNull(rs.Fields!PMTimeOut2), rs.Fields!PMTimeOut2, Empty)
cRemarks = IIf(Not IsNull(rs.Fields!remarks), rs.Fields!remarks, "")

cn.Close
cn.Open

With rsfullLogs
.Open "Select * FROM Temp_FullLogs", cn, 1, 2
.AddNew
.Fields!Name = IIf(Not IsNull(cName), cName, "")
.Fields!datelogin = IIf(Not IsNull(dlogindate), dlogindate, 0)
.Fields!DateLogout = IIf(Not IsNull(dLogOut), dLogOut, 0)
.Fields!AMTimeIn = IIf(Not IsNull(t_AmTimeIn), t_AmTimeIn, 0)
.Fields!AMTimeOut = IIf(Not IsNull(t_AmTimeOut), t_AmTimeOut, 0)
.Fields!PMTimeIn = IIf(Not IsNull(t_PMTimeIn), t_PMTimeIn, 0)
.Fields!PMTimeOut = IIf(Not IsNull(t_PmTimeOut), t_PmTimeOut, 0)
.Fields!AmTimeIn2 = IIf(Not IsNull(t_AmtimeIn2), t_AmtimeIn2, 0)
.Fields!AmTimeOut2 = IIf(Not IsNull(t_AmTimeOut), t_AmTimeOut2, 0)
.Fields!PMTimeIn2 = IIf(Not IsNull(t_PmTimeIn2), t_PmTimeIn2, 0)
.Fields!PMTimeOut2 = IIf(Not IsNull(t_PmTimeOut2), t_PmTimeOut2, 0)
.Fields!remarks = IIf(Not IsNull(cRemarks), cRemarks, "")
.Update
End With

rs.MoveNext
Loop
End If
Set rs.ActiveConnection = Nothing
rs.Close
cn.Close


Here is the code of one of my module. I empty the field of my date fields which is Amtimein,Amtimeout and etc.. and then I pass all the record of the first table to each field I declared, then i used IIf code which if the variable is empty it will return 0, I used 0 because if i used "" or Null it generates error. The problem have been quietly solved but the only thing now if the value is 0, it will save into the table with the value 12:00 am. Now my question is how can i null the record if the value is nothing? Please help..Thanks in advance...

 

[CP60]

Try
.Fields!PMTimeIn = NULL