SQL which looks for field with blank contents! 1

[BitCounter]

All:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;I have an SQL statement (see below) that generates a limited recordset.&nbsp;&nbsp;I want it to generate that recordset based on a particular field being empty (exit_status).&nbsp;&nbsp;In my database, if a particular record has anything in the exit_status field, then I do not want it in my current recordset.&nbsp;&nbsp;I have the following SQL statement and it returns no records.&nbsp;&nbsp;I essentially want the recordset returned where the field [exit_status] is equal to &quot;&quot;&nbsp;&nbsp;[nothing].&nbsp;&nbsp;What am I doing wrong?<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Set Rs1 = Conn1.Execute(&quot;SELECT Status From [Information Sheet] WHERE Exit_Status = '&quot;&quot;'&quot<br><br>Thanks!<br>

 

[DannyB]

Is anything null or not null ?? <p> <br><a href=mailto: > </a><br><a href=

http://dba.8m.com>

</a><br>

http://dba.8m.com

 

[BitCounter]

If the contents of the field are empty, then the field should be null....is that what you're asking?<br><br>

 

[skelly]

I'm not sure of the ODBC SQL syntax, but you could do something like: SELECT... WHERE LEN(TRIM(EXIT_STATUS)) = 0<br><br>-skelly<br>

 

[BitCounter]

DannyB and skelly,<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;You've both given me some ideas--thanks alot! I'll try those out tonight.<br><br>Renae

 

[Snaggs]

You have to use the 'Is Null' syntax for the query.&nbsp;&nbsp;Since there was some confusion about, an empty string and Null when I first started out, let me see if I can give you an explanation.<br><br>Let's first talk about NULL.&nbsp;&nbsp;NULL means that the field doesn't have a value according to the data type for the field so the value cannot be determined.<br><br>If you assign a field to an empty string like rsEmployee.Extension = &quot;&quot;, then the field is no longer NULL, but is actually an empty string.&nbsp;&nbsp;The value of the field CAN be determined, even though the value is an empty string.<br><br>...More random thoughts...<br><br>Typically when doing an insert into a table, you do not have to include all fields in the insert statement.&nbsp;&nbsp;Some of them like the PrimaryKey and required fields have to be included, but there are others that may not have to be included.&nbsp;&nbsp;For example, if you have a table with 20 fields and 10 of them you include in your insert statement, you will get values for the 10 that you included and the other 10 will be NULL.&nbsp;&nbsp;If you include the other fields and set them to a value, they will contain a valid value and they will not show up NULL.&nbsp;&nbsp;Some database engines will allow you to specify a default value for the field if it's not included in the insert statement.&nbsp;&nbsp;I think Access does this with numeric fields.<br><br>...Now back to your question...<br><br>Therefore your syntax for the query to test for NULL would look like this:<br><br>SELECT Status From [Information Sheet] WHERE Exit_Status Is Null<br><br>If you wanted to do just the opposite of this you can use the NOT word in the query.&nbsp;&nbsp;I mention this because the position of the NOT in the query may not be where you think it should be.&nbsp;&nbsp;The proper syntax for using NOT with IS NULL is like this:<br><br>SELECT Status From [Information Sheet] WHERE Not Exit_Status Is Null<br><br>I have a hard time remembering this myself and always have to try it until I get it.&nbsp;&nbsp;The one I always mix it up with is this format:<br><br>SELECT Status From [Information Sheet] WHERE Exit_Status Not Is Null<br><br>If you're using Access to look at the data by just opening the table, you can't tell the difference between a record that has a null value or a blank value.&nbsp;&nbsp;You actually have to run a query using Is Null or = &quot;&quot; in the where clause.<br><br>Hope that helps...<br> <p>Steve Meier<br><a href=mailto:sdmeier@jcn1.com>sdmeier@jcn1.com</a><br><a href= > </a><br>