SQL select statement, checking for null values in a Date Field in database.
I am using the select statement in SQL, I am trying to pull all records with a null value in the date field. Here is the current code i am using:
<%sql = "select d_susp, idea_no, s_name, title, eval_off, comments from suggest where action = 'A' and authority = 'Y' and (D_f_a_orl = '' or isnull(d_f_a_orl)) order by d_susp"%>
That you are seeing inside the parenthesis is 2 different ways i tried to pull records if that date field had a null value. First i tried 2 single quotes (that isnt a double quote in the code, but it looks like it on this page), then someone told me to try "< 1" and that didnt work, so i took that out, then i made a blank variable and checked to see if they were equal, that didnt workso i took that out, then i used isnul.
I know the other variables like action = 'A' and authority = 'Y' work because i have about 7 other SQL statements right before that searching using those as fields as criteria. In this sql statement the only difference is the d_f_a_orl field. In the other sql statements, i compared dates as in date() < d_f_a_or, but i have not tried to see if a date field has a null value. Is thier a special operator i have to use to complete this task? The error i get is this:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
/suggest/report2.asp, line 473
line 473 is the rs.execute statement on the next line. Hope you can help.
~Derrick
