Solved

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

Posted on 2011-09-08
4
473 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query and VBA 5 53
Trouble with <> 2 26
Compare a column in results by values left of decimal 2 22
sql select total by week ending 3 23
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

756 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