Invalid use of null Error, when ever i tried to check previous date using DTPicker.

Private Sub vAll()
On Error GoTo err:
If rs.State = adStateOpen Then rs.Close
Me.lvlDtr.ListItems.Clear
rs.Open "SELECT * FROM tblTimeLog where LogDate Between #" & Me.DTPicker1.Value & "# AND #" & Me.DTPicker2.Value & "#;", cn, adOpenKeyset, adLockPessimistic
Do While rs.EOF = False
Me.lvlDtr.ListItems.Add , , rs.Fields("EM_ID").Value
Me.lvlDtr.ListItems(Me.lvlDtr.ListItems.Count).SubItems(1) = rs.Fields("NAME").Value
Me.lvlDtr.ListItems(Me.lvlDtr.ListItems.Count).SubItems(2) = rs.Fields("LogDate").Value
Me.lvlDtr.ListItems(Me.lvlDtr.ListItems.Count).SubItems(3) = rs.Fields("TimeIn").Value
Me.lvlDtr.ListItems(Me.lvlDtr.ListItems.Count).SubItems(4) = rs.Fields("TimeOut").Value
Me.lvlDtr.ListItems(Me.lvlDtr.ListItems.Count).SubItems(5) = rs.Fields("E_ARRIVAL").Value
Me.lvlDtr.ListItems(Me.lvlDtr.ListItems.Count).SubItems(6) = rs.Fields("L_IN").Value
Me.lvlDtr.ListItems(Me.lvlDtr.ListItems.Count).SubItems(7) = rs.Fields("E_DEPT").Value
Me.lvlDtr.ListItems(Me.lvlDtr.ListItems.Count).SubItems(8) = rs.Fields("L_DEPT").Value
Me.lvlDtr.ListItems(Me.lvlDtr.ListItems.Count).SubItems(9) = rs.Fields("LATE").Value
Me.lvlDtr.ListItems(Me.lvlDtr.ListItems.Count).SubItems(10) = rs.Fields("OT").Value
Me.lvlDtr.ListItems(Me.lvlDtr.ListItems.Count).SubItems(11) = rs.Fields("REMARK").Value
rs.MoveNext
Loop
Exit Sub
err:
MsgBox err.Description, vbCritical, "Error"
Set rs = Nothing
End Sub

That means that one or more of those fields returned contains a null value
A simple fix for that is to append and empty string to the value returned from the db.

Example

Code:

---

Me.lvlDtr.ListItems(Me.lvlDtr.ListItems.Count).SubItems(2) = rs.Fields("LogDate").Value & ""

---

The empty string appended to the end turns a returned null into an empty string so it will just show up as blank in your listview rather than throwing an error

It always amazes me that people have Nulls in their databases but won't test for and handle them properly.

If a field should not contain nulls then define it as NOT NULL when you create the Table. A Null value means "not provided" which is an entirely different thing from "empty String" which might well be a legit distinct value. If not, then in addition to NOT NULL you can define a DEFAULT of an empty String. Problem goes away without weird hacks.

☺☺☺☺

Quote:

---

GoTo D:

---

Arghhhhhh!! Run!