[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

How do I handle a CDate that has a null value in a query?

Posted on 2011-09-08
4
Medium Priority
?
759 Views
Last Modified: 2012-08-14
I have a query that use CDate. All is fine if there is a value in the field but if it is null I get invalid use of null.
SELECT DISTINCTROW tblsubjects.subjectid, tblupdatetable.updateid, tblupdatetable.employeeid, tblupdatetable.clientid, tblupdatetable.Date, tblupdatetable.caseupdate, tblupdatetable.checkbox1, tblupdatetable.webview, tblupdatetable.Approved, tblupdatetable.recorddate, tblupdatetable.modifieddate, "Open" AS OpenText, tblsubjects.casenumber, IIf(IsNull([subjectlast]),IIf(IsNull([subjectfirst]),[subjectcompanyname],[subjectfirst]),IIf(IsNull([subjectfirst]),[subjectlast],[subjectfirst] & " " & [subjectlast])) AS casesub, tblsubjects.casestatus, "Web Files" AS Files, CDate(nz([modifieddate],[recorddate])) AS DateStamp
FROM tblsubjects INNER JOIN tblupdatetable ON tblsubjects.subjectid = tblupdatetable.subjectid
ORDER BY CDate(nz([modifieddate],[recorddate])) DESC;

Open in new window

0
Comment
Question by:seanlhall
  • 2
  • 2
4 Comments
 
LVL 28

Expert Comment

by:strickdd
ID: 36504008
Put, this around your datefield use:

CDate(IsNull(MyDateFieldHere, '1/1/1900'))
0
 

Author Comment

by:seanlhall
ID: 36504142
ok that lead me to this. Date Stamp: CDate(nz([modifieddate],'1/1/1900')) which works.
I need to display the modifieddate first if the modifieddate is Null then display the recorddate if recorddate is null then dispaly default of 1/1/1990. I came up with this but it does not work. I get  Wrong number of arguments used with function.

DateStamp: CDate(nz([modifieddate],[recorddate],'1/1/1990'))
0
 
LVL 28

Accepted Solution

by:
strickdd earned 2000 total points
ID: 36504320
CDate(nz(IsNull(midifieddate, IsNull(recorddate, '1/1/1900'))))
0
 

Author Comment

by:seanlhall
ID: 36504431
Thanks, you go me in the right direction. This worked for me.
CDate(nz([modifieddate],(nz([recorddate],'1/1/1990'))))
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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…

590 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