Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 683
  • Last Modified:

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

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
seanlhall
Asked:
seanlhall
  • 2
  • 2
1 Solution
 
strickddCommented:
Put, this around your datefield use:

CDate(IsNull(MyDateFieldHere, '1/1/1900'))
0
 
seanlhallAuthor Commented:
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
 
strickddCommented:
CDate(nz(IsNull(midifieddate, IsNull(recorddate, '1/1/1900'))))
0
 
seanlhallAuthor Commented:
Thanks, you go me in the right direction. This worked for me.
CDate(nz([modifieddate],(nz([recorddate],'1/1/1990'))))
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now