Returning Nulls from Date/Time field

Posted on 2009-04-28
Last Modified: 2012-05-06
Hi Experts

I have a query that returns null dates (even if i order them desc and specify <>"" ) It's in VB6 and excel 2003 - a date time field

Sql = "Select * from CLIENT_CONTROL where [PC_ClientID] = " & Val(CopyClientNr) _
        & " and [PC_ReminderDate] not  '' order by [PC_ReminderDate] desc "
If rs.recordset.nomatch = false then
         debug.print RS.recordset("PC_Client ID") & " ," & RS.Recordset("PC_ReminderDate")
End If

on this data
PC_PatID      PC_PracID      PC_DrID      PC_ReminderDate      
31425            2                        
I get : 31425 ,

I expected not to get nulls. Can anyone explain this?

Many Thanks
Question by:peterdarazs
    LVL 46

    Accepted Solution

    Hi Peter,

    Try using the isnull function


    Author Comment

    I have tried:  not isnull [PC_ReminderDate] , [PC_ReminderDate] <> "" , [PC_ReminderDate]  not = ""

    all without success
    LVL 11

    Assisted Solution


    Sql = "Select * from CLIENT_CONTROL where [PC_ClientID] = " & Val(CopyClientNr) _
            & " and [PC_ReminderDate] IS NOT NULL order by [PC_ReminderDate] desc "

    Open in new window


    Author Closing Comment

    Thanks , i used these concepts, but ultimately had to switch to using a recordset instead of ?datacomponent
    ie :
    Set dsCTRLRecord = CURDB.OpenRecordset(Sql, dbOpenDynaset)
    instead of :
    PatCTRLdata.recordsource = sql


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now