SQL select statemeny, checking for null values in a Date Field in database.

[derricks]

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

[M Owen]

Try: (D_f_a_orl & '' = '')

[hspc]

Hi
did you try this :

sql = "select d_susp, idea_no, s_name, title, eval_off, comments from suggest where action = 'A' and authority = 'Y' or isnull(d_f_a_orl) order by d_susp

[ITGURU]

In MS-Access Date field cannot store blank string because of this reason you cannot check date field with blank string.

For overcome your problem change your query to following:


select d_susp, idea_no, s_name, title, eval_off, comments from suggest where action = 'A' and authority = 'Y' and D_f_a_orl IS NULL order by d_susp