Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Returning Nulls from Date/Time field

Posted on 2009-04-28
Medium Priority
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
  • 2
LVL 46

Accepted Solution

tbsgadi earned 1000 total points
ID: 24248716
Hi Peter,

Try using the isnull function


Author Comment

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

all without success
LVL 11

Assisted Solution

bmatumbura earned 1000 total points
ID: 24248739

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

ID: 31575355
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


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

580 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