Results 1 to 5 of 5

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

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2017
    Posts
    8

    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

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Invalid use of null Error, when ever i tried to check previous date using DTPicke

    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

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Invalid use of null Error, when ever i tried to check previous date using DTPicke

    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.

  4. #4

    Thread Starter
    New Member
    Join Date
    Oct 2017
    Posts
    8

    Re: Invalid use of null Error, when ever i tried to check previous date using DTPicke

    ☺☺☺☺
    Last edited by sanjit.sam; Oct 24th, 2017 at 04:48 AM.

  5. #5
    Junior Member
    Join Date
    May 2017
    Posts
    22

    Re: Invalid use of null Error, when ever i tried to check previous date using DTPicke

    GoTo D:
    Arghhhhhh!! Run!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width